Adapter::upsert() - Code Metrics - Inspection of "Fix import of federated shares" - owncloud/core - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#29676)

by Tom

created 2017-11-24 11:44 UTC

Adapter::upsert() F

↳ Parent: Adapter

Complexity

Conditions	16
Paths	352

Size

Total Lines	95
Code Lines	59

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	16
eloc	59
nc	352
nop	3
dl	0
loc	95
rs	3.7109
c	0
b	0
f	0

How to fix Long Method Complexity

<?php
/**
 * @author Bart Visscher <[email protected]>
 * @author Joas Schilling <[email protected]>
 * @author Jonny007-MKD <[email protected]>
 * @author Morris Jobke <[email protected]>
 * @author Robin Appelman <[email protected]>
 * @author Thomas Müller <[email protected]>
 * @author Tom Needham <[email protected]>
 *
 * @copyright Copyright (c) 2017, ownCloud GmbH
 * @license AGPL-3.0
 *
 * This code is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License, version 3,
 * as published by the Free Software Foundation.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License, version 3,
 * along with this program.  If not, see <http://www.gnu.org/licenses/>
 *
 */

namespace OC\DB;
use Doctrine\DBAL\Exception\DriverException;
use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
use Doctrine\DBAL\Platforms\OraclePlatform;

/**
 * This handles the way we use to write queries, into something that can be
 * handled by the database abstraction layer.
 */
class Adapter {

	/**
	 * @var \OC\DB\Connection $conn
	 */
	protected $conn;

	public function __construct($conn) {
		$this->conn = $conn;
	}

	/**
	 * @param string $table name
	 * @return int id of last insert statement
	 */
	public function lastInsertId($table) {
		return $this->conn->realLastInsertId($table);
	}

	/**
	 * @param string $statement that needs to be changed so the db can handle it
	 * @return string changed statement
	 */
	public function fixupStatement($statement) {
		return $statement;
	}

	/**
	 * Create an exclusive read+write lock on a table
	 *
	 * @param string $tableName
	 * @since 9.1.0
	 */
	public function lockTable($tableName) {
		$this->conn->beginTransaction();
		$this->conn->executeUpdate('LOCK TABLE `' .$tableName . '` IN EXCLUSIVE MODE');
	}

	/**
	 * Release a previous acquired lock again
	 *
	 * @since 9.1.0
	 */
	public function unlockTable() {
		$this->conn->commit();
	}

	/**
	 * Insert a row if the matching row does not exists.
	 *
	 * @param string $table The table name (will replace *PREFIX* with the actual prefix)
	 * @param array $input data that should be inserted into the table  (column name => value)
	 * @param array|null $compare List of values that should be checked for "if not exists"
	 *				If this is null or an empty array, all keys of $input will be compared
	 *				Please note: text fields (clob) must not be used in the compare array
	 * @return int number of inserted rows
	 * @throws \Doctrine\DBAL\DBALException
	 */
	public function insertIfNotExist($table, $input, array $compare = null) {
		if (empty($compare)) {
			$compare = array_keys($input);
		}
		$query = 'INSERT INTO `' . $table . '` (`'
			. implode('`,`', array_keys($input)) . '`) SELECT '
			. str_repeat('?,', count($input) - 1) . '? ' // Is there a prettier alternative?
			. 'FROM `' . $table . '` WHERE ';

		$inserts = array_values($input);
		foreach ($compare as $key) {

			$query .= '`' . $key . '`';
			if (is_null($input[$key])) {
				$query .= ' IS NULL AND ';
			} else {
				$inserts[] = $input[$key];
				$query .= ' = ? AND ';
			}
		}
		$query = substr($query, 0, strlen($query) - 5);
		$query .= ' HAVING COUNT(*) = 0';
		return $this->conn->executeUpdate($query, $inserts);
	}

	/**
	 * Inserts, or updates a row into the database. Returns the inserted or updated rows
	 * @param $table string table name including **PREFIX**
	 * @param $input array the key=>value pairs to insert into the db row
	 * @param $compare array columns that should be compared to look for existing arrays
	 * @return int the number of rows affected by the operation
	 * @throws DriverException|\RuntimeException
	 */
	public function upsert($table, $input, $compare) {

		$this->conn->beginTransaction();
		$done = false;

		if (empty($compare)) {
			$compare = array_keys($input);
		}

		// Construct the update query
		$qbu = $this->conn->getQueryBuilder();
		$qbu->update($table);
		foreach($input as $col => $val) {
			$qbu->set($col, $qbu->createParameter($col))
			->setParameter($col, $val);
		}
		foreach($compare as $key) {
			if (is_null($input[$key]) || ($input[$key] === '' && $this->conn->getDatabasePlatform() instanceof OraclePlatform)) {
				$qbu->andWhere($qbu->expr()->isNull($key));
			} else {
				if($this->conn->getDatabasePlatform() instanceof OraclePlatform) {
					$qbu->andWhere(
						$qbu->expr()->eq(
							// needs to cast to char in order to compare with char
							$qbu->createFunction('to_char(`'.$key.'`)'), // TODO does this handle empty strings on oracle correclty
							$qbu->expr()->literal($input[$key])));
				} else {
					$qbu->andWhere(
						$qbu->expr()->eq(
							$key,
							$qbu->expr()->literal($input[$key])));
				}
			}
		}

		// Construct the insert query
		$qbi = $this->conn->getQueryBuilder();
		$qbi->insert($table);
		foreach($input as $c => $v) {
			$qbi->setValue($c, $qbi->createParameter($c))
				->setParameter($c, $v);
		}

		$rows = 0;
		$count = 0;
		// Attempt 5 times before failing the upsert
		$maxTry = 5;

		while(!$done && $count < $maxTry) {
			try {
				// Try to update
				$rows = $qbu->execute();
			} catch (DriverException $e) {
				// Skip deadlock and retry
				// @TODO when we update to DBAL 2.6 we can use DeadlockExceptions here
				if($e->getErrorCode() == 1213) {
					$count++;
					continue;
				} else {
					// We should catch other exceptions up the stack
					$this->conn->rollBack();
					throw $e;
				}
			}
			if($rows > 0) {
				// We altered some rows, return
				$done = true;
			} else {
				// Try the insert
				$this->conn->beginTransaction();
				try {
					// Execute the insert query
					$rows = $qbi->execute();
					$done = $rows > 0;
				} catch (UniqueConstraintViolationException $e) {
					// Catch the unique violation and try the loop again
					$count++;
				}
				// Other exceptions are not caught, they should be caught up the stack
				$this->conn->commit();
			}
		}

		// Pass through failures correctly
		if($count === $maxTry) {
			$params = implode(',', $input);
			$updateQuery = $qbu->getSQL();
			$insertQuery = $qbi->getSQL();
			throw new \RuntimeException("DB upsert failed after $count attempts. UpdateQuery: $updateQuery InsertQuery: $insertQuery");
		}

		$this->conn->commit();
		return $rows;

	}
}


1		<?php
2		/**
3		* @author Bart Visscher <[email protected]>
4		* @author Joas Schilling <[email protected]>
5		* @author Jonny007-MKD <[email protected]>
6		* @author Morris Jobke <[email protected]>
7		* @author Robin Appelman <[email protected]>
8		* @author Thomas Müller <[email protected]>
9		* @author Tom Needham <[email protected]>
10		*
11		* @copyright Copyright (c) 2017, 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
28		namespace OC\DB;
29		use Doctrine\DBAL\Exception\DriverException;
30		use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
31		use 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		*/
37		class 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	View Code Duplication	foreach ($compare as $key) {
		0 ignored issues – show Duplication introduced 2015-11-21 07:39 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
106		$query .= '`' . $key . '`';
107		if (is_null($input[$key])) {
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
129		$this->conn->beginTransaction();
130		$done = false;
131
132		if (empty($compare)) {
133		$compare = array_keys($input);
134		}
135
136		// Construct the update query
137		$qbu = $this->conn->getQueryBuilder();
138		$qbu->update($table);
139		foreach($input as $col => $val) {
140		$qbu->set($col, $qbu->createParameter($col))
141		->setParameter($col, $val);
142		}
143		foreach($compare as $key) {
144		if (is_null($input[$key]) \|\| ($input[$key] === '' && $this->conn->getDatabasePlatform() instanceof OraclePlatform)) {
145		$qbu->andWhere($qbu->expr()->isNull($key));
146		} else {
147		if($this->conn->getDatabasePlatform() instanceof OraclePlatform) {
148		$qbu->andWhere(
149		$qbu->expr()->eq(
150		// needs to cast to char in order to compare with char
151		$qbu->createFunction('to_char(`'.$key.'`)'), // TODO does this handle empty strings on oracle correclty
152		$qbu->expr()->literal($input[$key])));
153		} else {
154		$qbu->andWhere(
155		$qbu->expr()->eq(
156		$key,
157		$qbu->expr()->literal($input[$key])));
158		}
159		}
160		}
161
162		// Construct the insert query
163		$qbi = $this->conn->getQueryBuilder();
164		$qbi->insert($table);
165		foreach($input as $c => $v) {
166		$qbi->setValue($c, $qbi->createParameter($c))
167		->setParameter($c, $v);
168		}
169
170		$rows = 0;
171		$count = 0;
172		// Attempt 5 times before failing the upsert
173		$maxTry = 5;
174
175		while(!$done && $count < $maxTry) {
176		try {
177		// Try to update
178		$rows = $qbu->execute();
179		} catch (DriverException $e) {
180		// Skip deadlock and retry
181		// @TODO when we update to DBAL 2.6 we can use DeadlockExceptions here
182		if($e->getErrorCode() == 1213) {
183		$count++;
184		continue;
185		} else {
186		// We should catch other exceptions up the stack
187		$this->conn->rollBack();
188		throw $e;
189		}
190		}
191		if($rows > 0) {
192		// We altered some rows, return
193		$done = true;
194		} else {
195		// Try the insert
196		$this->conn->beginTransaction();
197		try {
198		// Execute the insert query
199		$rows = $qbi->execute();
200		$done = $rows > 0;
201		} catch (UniqueConstraintViolationException $e) {
202		// Catch the unique violation and try the loop again
203		$count++;
204		}
205		// Other exceptions are not caught, they should be caught up the stack
206		$this->conn->commit();
207		}
208		}
209
210		// Pass through failures correctly
211		if($count === $maxTry) {
212		$params = implode(',', $input);
213		$updateQuery = $qbu->getSQL();
214		$insertQuery = $qbi->getSQL();
215		throw new \RuntimeException("DB upsert failed after $count attempts. UpdateQuery: $updateQuery InsertQuery: $insertQuery");
216		}
217
218		$this->conn->commit();
219		return $rows;
220
221		}
222		}
223

owncloud / core

Pull Request — master (#29676)

Adapter::upsert() F

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like