1<?php 2/** 3 * @author Bart Visscher <bartv@thisnet.nl> 4 * @author Joas Schilling <coding@schilljs.com> 5 * @author Jonny007-MKD <1-23-4-5@web.de> 6 * @author Morris Jobke <hey@morrisjobke.de> 7 * @author Robin Appelman <icewind@owncloud.com> 8 * @author Thomas Müller <thomas.mueller@tmit.eu> 9 * @author Tom Needham <tom@owncloud.com> 10 * 11 * @copyright Copyright (c) 2018, ownCloud GmbH 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 28namespace OC\DB; 29use Doctrine\DBAL\Exception\DriverException; 30use Doctrine\DBAL\Exception\UniqueConstraintViolationException; 31use Doctrine\DBAL\Platforms\OraclePlatform; 32 33/** 34 * This handles the way we use to write queries, into something that can be 35 * handled by the database abstraction layer. 36 */ 37class Adapter { 38 39 /** 40 * @var \OC\DB\Connection $conn 41 */ 42 protected $conn; 43 44 public function __construct($conn) { 45 $this->conn = $conn; 46 } 47 48 /** 49 * @param string $table name 50 * @return int id of last insert statement 51 */ 52 public function lastInsertId($table) { 53 return $this->conn->realLastInsertId($table); 54 } 55 56 /** 57 * @param string $statement that needs to be changed so the db can handle it 58 * @return string changed statement 59 */ 60 public function fixupStatement($statement) { 61 return $statement; 62 } 63 64 /** 65 * Create an exclusive read+write lock on a table 66 * 67 * @param string $tableName 68 * @since 9.1.0 69 */ 70 public function lockTable($tableName) { 71 $this->conn->beginTransaction(); 72 $this->conn->executeUpdate('LOCK TABLE `' .$tableName . '` IN EXCLUSIVE MODE'); 73 } 74 75 /** 76 * Release a previous acquired lock again 77 * 78 * @since 9.1.0 79 */ 80 public function unlockTable() { 81 $this->conn->commit(); 82 } 83 84 /** 85 * Insert a row if the matching row does not exists. 86 * 87 * @param string $table The table name (will replace *PREFIX* with the actual prefix) 88 * @param array $input data that should be inserted into the table (column name => value) 89 * @param array|null $compare List of values that should be checked for "if not exists" 90 * If this is null or an empty array, all keys of $input will be compared 91 * Please note: text fields (clob) must not be used in the compare array 92 * @return int number of inserted rows 93 * @throws \Doctrine\DBAL\DBALException 94 */ 95 public function insertIfNotExist($table, $input, array $compare = null) { 96 if (empty($compare)) { 97 $compare = \array_keys($input); 98 } 99 $query = 'INSERT INTO `' . $table . '` (`' 100 . \implode('`,`', \array_keys($input)) . '`) SELECT ' 101 . \str_repeat('?,', \count($input) - 1) . '? ' // Is there a prettier alternative? 102 . 'FROM `' . $table . '` WHERE '; 103 104 $inserts = \array_values($input); 105 foreach ($compare as $key) { 106 $query .= '`' . $key . '`'; 107 if ($input[$key] === null) { 108 $query .= ' IS NULL AND '; 109 } else { 110 $inserts[] = $input[$key]; 111 $query .= ' = ? AND '; 112 } 113 } 114 $query = \substr($query, 0, \strlen($query) - 5); 115 $query .= ' HAVING COUNT(*) = 0'; 116 return $this->conn->executeUpdate($query, $inserts); 117 } 118 119 /** 120 * Inserts, or updates a row into the database. Returns the inserted or updated rows 121 * @param $table string table name including **PREFIX** 122 * @param $input array the key=>value pairs to insert into the db row 123 * @param $compare array columns that should be compared to look for existing arrays 124 * @return int the number of rows affected by the operation 125 * @throws DriverException|\RuntimeException 126 */ 127 public function upsert($table, $input, $compare) { 128 $this->conn->beginTransaction(); 129 $done = false; 130 131 if (empty($compare)) { 132 $compare = \array_keys($input); 133 } 134 135 // Construct the update query 136 $qbu = $this->conn->getQueryBuilder(); 137 $qbu->update($table); 138 foreach ($input as $col => $val) { 139 $qbu->set($col, $qbu->createParameter($col)) 140 ->setParameter($col, $val); 141 } 142 foreach ($compare as $key) { 143 if ($input[$key] === null || ($input[$key] === '' && $this->conn->getDatabasePlatform() instanceof OraclePlatform)) { 144 $qbu->andWhere($qbu->expr()->isNull($key)); 145 } else { 146 if ($this->conn->getDatabasePlatform() instanceof OraclePlatform) { 147 $qbu->andWhere( 148 $qbu->expr()->eq( 149 // needs to cast to char in order to compare with char 150 $qbu->createFunction('to_char(`'.$key.'`)'), // TODO does this handle empty strings on oracle correclty 151 $qbu->expr()->literal($input[$key]) 152 ) 153 ); 154 } else { 155 $qbu->andWhere( 156 $qbu->expr()->eq( 157 $key, 158 $qbu->expr()->literal($input[$key]) 159 ) 160 ); 161 } 162 } 163 } 164 165 // Construct the insert query 166 $qbi = $this->conn->getQueryBuilder(); 167 $qbi->insert($table); 168 foreach ($input as $c => $v) { 169 $qbi->setValue($c, $qbi->createParameter($c)) 170 ->setParameter($c, $v); 171 } 172 173 $rows = 0; 174 $count = 0; 175 // Attempt 5 times before failing the upsert 176 $maxTry = 5; 177 178 while (!$done && $count < $maxTry) { 179 try { 180 // Try to update 181 $rows = $qbu->execute(); 182 } catch (DriverException $e) { 183 // Skip deadlock and retry 184 // @TODO when we update to DBAL 2.6 we can use DeadlockExceptions here 185 if ($e->getErrorCode() == 1213) { 186 $count++; 187 continue; 188 } else { 189 // We should catch other exceptions up the stack 190 $this->conn->rollBack(); 191 throw $e; 192 } 193 } 194 if ($rows > 0) { 195 // We altered some rows, return 196 $done = true; 197 } else { 198 // Try the insert 199 $this->conn->beginTransaction(); 200 try { 201 // Execute the insert query 202 $rows = $qbi->execute(); 203 $done = $rows > 0; 204 } catch (UniqueConstraintViolationException $e) { 205 // Catch the unique violation and try the loop again 206 $count++; 207 } 208 // Other exceptions are not caught, they should be caught up the stack 209 $this->conn->commit(); 210 } 211 } 212 213 // Pass through failures correctly 214 if ($count === $maxTry) { 215 $params = \implode(',', $input); 216 $updateQuery = $qbu->getSQL(); 217 $insertQuery = $qbi->getSQL(); 218 throw new \RuntimeException("DB upsert failed after $count attempts. UpdateQuery: $updateQuery InsertQuery: $insertQuery"); 219 } 220 221 $this->conn->commit(); 222 return $rows; 223 } 224} 225