From 88cd61521459a18599407f83347f1d6a0e7700cc Mon Sep 17 00:00:00 2001 From: Robin McCorkell Date: Mon, 24 Aug 2015 13:21:09 +0100 Subject: Introduce IDBConnection::setValues() setValues() attempts to insert a new row, or failing that, update an existing row. The ability to set preconditions is also available. --- lib/private/db/connection.php | 47 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 47 insertions(+) (limited to 'lib/private/db') diff --git a/lib/private/db/connection.php b/lib/private/db/connection.php index 28bf3b6e05b..b1c1c12cb49 100644 --- a/lib/private/db/connection.php +++ b/lib/private/db/connection.php @@ -32,6 +32,7 @@ use Doctrine\Common\EventManager; use OC\DB\QueryBuilder\ExpressionBuilder; use OC\DB\QueryBuilder\QueryBuilder; use OCP\IDBConnection; +use OCP\PreconditionNotMetException; class Connection extends \Doctrine\DBAL\Connection implements IDBConnection { /** @@ -241,6 +242,52 @@ class Connection extends \Doctrine\DBAL\Connection implements IDBConnection { return $this->adapter->insertIfNotExist($table, $input, $compare); } + /** + * Insert or update a row value + * + * @param string $table + * @param array $keys (column name => value) + * @param array $values (column name => value) + * @param array $updatePreconditionValues ensure values match preconditions (column name => value) + * @return int number of new rows + * @throws \Doctrine\DBAL\DBALException + * @throws PreconditionNotMetException + */ + public function setValues($table, array $keys, array $values, array $updatePreconditionValues = []) { + try { + $insertQb = $this->getQueryBuilder(); + $insertQb->insert($table) + ->values( + array_map(function($value) use ($insertQb) { + return $insertQb->createNamedParameter($value); + }, array_merge($keys, $values)) + ); + return $insertQb->execute(); + } catch (\Doctrine\DBAL\Exception\UniqueConstraintViolationException $e) { + // value already exists, try update + $updateQb = $this->getQueryBuilder(); + $updateQb->update($table); + foreach ($values as $name => $value) { + $updateQb->set($name, $updateQb->createNamedParameter($value)); + } + $where = $updateQb->expr()->andx(); + $whereValues = array_merge($keys, $updatePreconditionValues); + foreach ($whereValues as $name => $value) { + $where->add($updateQb->expr()->eq( + $name, $updateQb->createNamedParameter($value) + )); + } + $updateQb->where($where); + $affected = $updateQb->execute(); + + if ($affected === 0) { + throw new PreconditionNotMetException(); + } + + return 0; + } + } + /** * returns the error code and message as a string for logging * works with DoctrineException -- cgit v1.2.3 From 895fd49fb26da4c00ab8c64a45d58dae41fd7962 Mon Sep 17 00:00:00 2001 From: Robin Appelman Date: Fri, 15 Jan 2016 16:13:03 +0100 Subject: also handle not null violations --- lib/private/db/connection.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'lib/private/db') diff --git a/lib/private/db/connection.php b/lib/private/db/connection.php index b1c1c12cb49..2d7545df36e 100644 --- a/lib/private/db/connection.php +++ b/lib/private/db/connection.php @@ -263,7 +263,7 @@ class Connection extends \Doctrine\DBAL\Connection implements IDBConnection { }, array_merge($keys, $values)) ); return $insertQb->execute(); - } catch (\Doctrine\DBAL\Exception\UniqueConstraintViolationException $e) { + } catch (\Doctrine\DBAL\Exception\ConstraintViolationException $e) { // value already exists, try update $updateQb = $this->getQueryBuilder(); $updateQb->update($table); -- cgit v1.2.3 From ebd15fd5edbce1c173af885e61f9a1fbd1b4e33e Mon Sep 17 00:00:00 2001 From: Robin Appelman Date: Fri, 15 Jan 2016 16:47:17 +0100 Subject: handle bool in setValue --- lib/private/db/connection.php | 16 +++++++++++++--- 1 file changed, 13 insertions(+), 3 deletions(-) (limited to 'lib/private/db') diff --git a/lib/private/db/connection.php b/lib/private/db/connection.php index 2d7545df36e..a961c84f372 100644 --- a/lib/private/db/connection.php +++ b/lib/private/db/connection.php @@ -242,6 +242,16 @@ class Connection extends \Doctrine\DBAL\Connection implements IDBConnection { return $this->adapter->insertIfNotExist($table, $input, $compare); } + private function getType($value) { + if (is_bool($value)) { + return \PDO::PARAM_BOOL; + } else if (is_int($value)) { + return \PDO::PARAM_INT; + } else { + return \PDO::PARAM_STR; + } + } + /** * Insert or update a row value * @@ -259,7 +269,7 @@ class Connection extends \Doctrine\DBAL\Connection implements IDBConnection { $insertQb->insert($table) ->values( array_map(function($value) use ($insertQb) { - return $insertQb->createNamedParameter($value); + return $insertQb->createNamedParameter($value, $this->getType($value)); }, array_merge($keys, $values)) ); return $insertQb->execute(); @@ -268,13 +278,13 @@ class Connection extends \Doctrine\DBAL\Connection implements IDBConnection { $updateQb = $this->getQueryBuilder(); $updateQb->update($table); foreach ($values as $name => $value) { - $updateQb->set($name, $updateQb->createNamedParameter($value)); + $updateQb->set($name, $updateQb->createNamedParameter($value), $this->getType($value)); } $where = $updateQb->expr()->andx(); $whereValues = array_merge($keys, $updatePreconditionValues); foreach ($whereValues as $name => $value) { $where->add($updateQb->expr()->eq( - $name, $updateQb->createNamedParameter($value) + $name, $updateQb->createNamedParameter($value, $this->getType($value)) )); } $updateQb->where($where); -- cgit v1.2.3 From 58afddfaa585fdb9efb34c01d1a5fa6282ed2bd1 Mon Sep 17 00:00:00 2001 From: Robin Appelman Date: Mon, 18 Jan 2016 16:03:41 +0100 Subject: allow comparing clob using expressionbuilder->eq if you explicitly say you're comparing strings --- lib/private/db/connection.php | 4 ++- lib/private/db/querybuilder/expressionbuilder.php | 8 ++++-- .../db/querybuilder/ociexpressionbuilder.php | 33 ++++++++++++++++++++++ lib/private/db/querybuilder/querybuilder.php | 7 ++++- lib/public/db/querybuilder/iexpressionbuilder.php | 4 ++- 5 files changed, 50 insertions(+), 6 deletions(-) create mode 100644 lib/private/db/querybuilder/ociexpressionbuilder.php (limited to 'lib/private/db') diff --git a/lib/private/db/connection.php b/lib/private/db/connection.php index a961c84f372..6c4f518dfb5 100644 --- a/lib/private/db/connection.php +++ b/lib/private/db/connection.php @@ -284,7 +284,9 @@ class Connection extends \Doctrine\DBAL\Connection implements IDBConnection { $whereValues = array_merge($keys, $updatePreconditionValues); foreach ($whereValues as $name => $value) { $where->add($updateQb->expr()->eq( - $name, $updateQb->createNamedParameter($value, $this->getType($value)) + $name, + $updateQb->createNamedParameter($value, $this->getType($value)), + $this->getType($value) )); } $updateQb->where($where); diff --git a/lib/private/db/querybuilder/expressionbuilder.php b/lib/private/db/querybuilder/expressionbuilder.php index de10f69b361..1e86db5a081 100644 --- a/lib/private/db/querybuilder/expressionbuilder.php +++ b/lib/private/db/querybuilder/expressionbuilder.php @@ -27,10 +27,10 @@ use OCP\IDBConnection; class ExpressionBuilder implements IExpressionBuilder { /** @var \Doctrine\DBAL\Query\Expression\ExpressionBuilder */ - private $expressionBuilder; + protected $expressionBuilder; /** @var QuoteHelper */ - private $helper; + protected $helper; /** * Initializes a new ExpressionBuilder. @@ -109,10 +109,12 @@ class ExpressionBuilder implements IExpressionBuilder { * * @param mixed $x The left expression. * @param mixed $y The right expression. + * @param int|null $type one of the \PDO::PARAM_* constants + * required when comparing text fields for oci compatibility * * @return string */ - public function eq($x, $y) { + public function eq($x, $y, $type = null) { $x = $this->helper->quoteColumnName($x); $y = $this->helper->quoteColumnName($y); return $this->expressionBuilder->eq($x, $y); diff --git a/lib/private/db/querybuilder/ociexpressionbuilder.php b/lib/private/db/querybuilder/ociexpressionbuilder.php new file mode 100644 index 00000000000..742611bf719 --- /dev/null +++ b/lib/private/db/querybuilder/ociexpressionbuilder.php @@ -0,0 +1,33 @@ + + * + * @copyright Copyright (c) 2016, ownCloud, Inc. + * @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 + * + */ + +namespace OC\DB\QueryBuilder; + +class OCIExpressionBuilder extends ExpressionBuilder { + public function eq($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + if ($type === \PDO::PARAM_STR) { + $x = new QueryFunction('to_char(' . $x . ')'); + } + return $this->expressionBuilder->eq($x, $y); + } +} diff --git a/lib/private/db/querybuilder/querybuilder.php b/lib/private/db/querybuilder/querybuilder.php index 492e9bc9abf..42b290b90e7 100644 --- a/lib/private/db/querybuilder/querybuilder.php +++ b/lib/private/db/querybuilder/querybuilder.php @@ -21,6 +21,7 @@ namespace OC\DB\QueryBuilder; +use OC\DB\OracleConnection; use OCP\DB\QueryBuilder\IQueryBuilder; use OCP\DB\QueryBuilder\IQueryFunction; use OCP\DB\QueryBuilder\IParameter; @@ -82,7 +83,11 @@ class QueryBuilder implements IQueryBuilder { * @return \OCP\DB\QueryBuilder\IExpressionBuilder */ public function expr() { - return new ExpressionBuilder($this->connection); + if ($this->connection instanceof OracleConnection) { + return new OCIExpressionBuilder($this->connection); + } else { + return new ExpressionBuilder($this->connection); + } } /** diff --git a/lib/public/db/querybuilder/iexpressionbuilder.php b/lib/public/db/querybuilder/iexpressionbuilder.php index ae62694fcaf..0549d3f0125 100644 --- a/lib/public/db/querybuilder/iexpressionbuilder.php +++ b/lib/public/db/querybuilder/iexpressionbuilder.php @@ -84,11 +84,13 @@ interface IExpressionBuilder { * * @param mixed $x The left expression. * @param mixed $y The right expression. + * @param int|null $type @since 9.0.0 one of the \PDO::PARAM_* constants + * required when comparing text fields for oci compatibility. * * @return string * @since 8.2.0 */ - public function eq($x, $y); + public function eq($x, $y, $type = null); /** * Creates a non equality comparison expression with the given arguments. -- cgit v1.2.3