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