From d537cae06345af37857b20a1a315229881e1c6c7 Mon Sep 17 00:00:00 2001 From: Arthur Schiwon Date: Thu, 4 Feb 2016 18:34:01 +0100 Subject: cleanup jobs for comments and comment read marks --- apps/files/appinfo/info.xml | 2 +- apps/files/appinfo/install.php | 2 +- apps/files/appinfo/update.php | 2 +- .../lib/backgroundjob/deleteorphaneditems.php | 129 +++++++++++ .../lib/backgroundjob/deleteorphanedtagsjob.php | 105 --------- .../backgroundjob/DeleteOrphanedItemsJobTest.php | 258 +++++++++++++++++++++ .../backgroundjob/DeleteOrphanedTagsJobTest.php | 158 ------------- 7 files changed, 390 insertions(+), 266 deletions(-) create mode 100644 apps/files/lib/backgroundjob/deleteorphaneditems.php delete mode 100644 apps/files/lib/backgroundjob/deleteorphanedtagsjob.php create mode 100644 apps/files/tests/backgroundjob/DeleteOrphanedItemsJobTest.php delete mode 100644 apps/files/tests/backgroundjob/DeleteOrphanedTagsJobTest.php (limited to 'apps/files') diff --git a/apps/files/appinfo/info.xml b/apps/files/appinfo/info.xml index 136baa2ed07..b31232b799a 100644 --- a/apps/files/appinfo/info.xml +++ b/apps/files/appinfo/info.xml @@ -6,7 +6,7 @@ AGPL Robin Appelman, Vincent Petry - 1.4.2 + 1.4.3 diff --git a/apps/files/appinfo/install.php b/apps/files/appinfo/install.php index b9a893d1ee8..ae08e21a22e 100644 --- a/apps/files/appinfo/install.php +++ b/apps/files/appinfo/install.php @@ -21,4 +21,4 @@ // Cron job for scanning user storages \OC::$server->getJobList()->add('OCA\Files\BackgroundJob\ScanFiles'); -\OC::$server->getJobList()->add('OCA\Files\BackgroundJob\DeleteOrphanedTagsJob'); +\OC::$server->getJobList()->add('OCA\Files\BackgroundJob\DeleteOrphanedItems'); diff --git a/apps/files/appinfo/update.php b/apps/files/appinfo/update.php index 003f6916ac5..cb682cbc426 100644 --- a/apps/files/appinfo/update.php +++ b/apps/files/appinfo/update.php @@ -99,4 +99,4 @@ if ($installedVersion === '1.1.9' && ( // Add cron job for scanning user storages \OC::$server->getJobList()->add('OCA\Files\BackgroundJob\ScanFiles'); -\OC::$server->getJobList()->add('OCA\Files\BackgroundJob\DeleteOrphanedTagsJob'); +\OC::$server->getJobList()->add('OCA\Files\BackgroundJob\DeleteOrphanedItems'); diff --git a/apps/files/lib/backgroundjob/deleteorphaneditems.php b/apps/files/lib/backgroundjob/deleteorphaneditems.php new file mode 100644 index 00000000000..37c05ce214f --- /dev/null +++ b/apps/files/lib/backgroundjob/deleteorphaneditems.php @@ -0,0 +1,129 @@ + + * + * @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 OCA\Files\BackgroundJob; + +use OC\BackgroundJob\TimedJob; + +/** + * Delete all share entries that have no matching entries in the file cache table. + */ +class DeleteOrphanedItems extends TimedJob { + + /** @var \OCP\IDBConnection */ + protected $connection; + + /** @var \OCP\ILogger */ + protected $logger; + + /** + * Default interval in minutes + * + * @var int $defaultIntervalMin + **/ + protected $defaultIntervalMin = 60; + + /** + * sets the correct interval for this timed job + */ + public function __construct() { + $this->interval = $this->defaultIntervalMin * 60; + $this->connection = \OC::$server->getDatabaseConnection(); + $this->logger = \OC::$server->getLogger(); + } + + /** + * Makes the background job do its work + * + * @param array $argument unused argument + */ + public function run($argument) { + $this->cleanSystemTags(); + $this->cleanUserTags(); + $this->cleanComments(); + $this->cleanCommentMarkers(); + } + + /** + * Deleting orphaned system tag mappings + * + * @return int Number of deleted entries + */ + protected function cleanUp($table, $idCol, $typeCol) { + $subQuery = $this->connection->getQueryBuilder(); + $subQuery->select($subQuery->expr()->literal('1')) + ->from('filecache', 'f') + ->where($subQuery->expr()->eq($idCol, 'f.fileid')); + + $query = $this->connection->getQueryBuilder(); + $deletedEntries = $query->delete($table) + ->where($query->expr()->eq($typeCol, $query->expr()->literal('files'))) + ->andWhere($query->expr()->isNull($query->createFunction('(' . $subQuery->getSql() . ')'))) + ->execute(); + + return $deletedEntries; + } + + /** + * Deleting orphaned system tag mappings + * + * @return int Number of deleted entries + */ + protected function cleanSystemTags() { + $deletedEntries = $this->cleanUp('systemtag_object_mapping', 'objectid', 'objecttype'); + $this->logger->debug("$deletedEntries orphaned system tag relations deleted", ['app' => 'DeleteOrphanedItems']); + return $deletedEntries; + } + + /** + * Deleting orphaned user tag mappings + * + * @return int Number of deleted entries + */ + protected function cleanUserTags() { + $deletedEntries = $this->cleanUp('vcategory_to_object', 'objid', 'type'); + $this->logger->debug("$deletedEntries orphaned user tag relations deleted", ['app' => 'DeleteOrphanedItems']); + return $deletedEntries; + } + + /** + * Deleting orphaned comments + * + * @return int Number of deleted entries + */ + protected function cleanComments() { + $deletedEntries = $this->cleanUp('comments', 'object_id', 'object_type'); + $this->logger->debug("$deletedEntries orphaned comments deleted", ['app' => 'DeleteOrphanedItems']); + return $deletedEntries; + } + + /** + * Deleting orphaned comment read markers + * + * @return int Number of deleted entries + */ + protected function cleanCommentMarkers() { + $deletedEntries = $this->cleanUp('comments_read_markers', 'object_id', 'object_type'); + $this->logger->debug("$deletedEntries orphaned comment read marks deleted", ['app' => 'DeleteOrphanedItems']); + return $deletedEntries; + } + +} diff --git a/apps/files/lib/backgroundjob/deleteorphanedtagsjob.php b/apps/files/lib/backgroundjob/deleteorphanedtagsjob.php deleted file mode 100644 index 33f455b5b40..00000000000 --- a/apps/files/lib/backgroundjob/deleteorphanedtagsjob.php +++ /dev/null @@ -1,105 +0,0 @@ - - * - * @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 OCA\Files\BackgroundJob; - -use OC\BackgroundJob\TimedJob; - -/** - * Delete all share entries that have no matching entries in the file cache table. - */ -class DeleteOrphanedTagsJob extends TimedJob { - - /** @var \OCP\IDBConnection */ - protected $connection; - - /** @var \OCP\ILogger */ - protected $logger; - - /** - * Default interval in minutes - * - * @var int $defaultIntervalMin - **/ - protected $defaultIntervalMin = 60; - - /** - * sets the correct interval for this timed job - */ - public function __construct() { - $this->interval = $this->defaultIntervalMin * 60; - $this->connection = \OC::$server->getDatabaseConnection(); - $this->logger = \OC::$server->getLogger(); - } - - /** - * Makes the background job do its work - * - * @param array $argument unused argument - */ - public function run($argument) { - $this->cleanSystemTags(); - $this->cleanUserTags(); - } - - /** - * Deleting orphaned system tag mappings - * - * @return int Number of deleted entries - */ - protected function cleanSystemTags() { - $subQuery = $this->connection->getQueryBuilder(); - $subQuery->select($subQuery->expr()->literal('1')) - ->from('filecache', 'f') - ->where($subQuery->expr()->eq('objectid', 'f.fileid')); - - $query = $this->connection->getQueryBuilder(); - $deletedEntries = $query->delete('systemtag_object_mapping') - ->where($query->expr()->eq('objecttype', $query->expr()->literal('files'))) - ->andWhere($query->expr()->isNull($query->createFunction('(' . $subQuery->getSql() . ')'))) - ->execute(); - - $this->logger->debug("$deletedEntries orphaned system tag relations deleted", ['app' => 'DeleteOrphanedTagsJob']); - return $deletedEntries; - } - - /** - * Deleting orphaned user tag mappings - * - * @return int Number of deleted entries - */ - protected function cleanUserTags() { - $subQuery = $this->connection->getQueryBuilder(); - $subQuery->select($subQuery->expr()->literal('1')) - ->from('filecache', 'f') - ->where($subQuery->expr()->eq('objid', 'f.fileid')); - - $query = $this->connection->getQueryBuilder(); - $deletedEntries = $query->delete('vcategory_to_object') - ->where($query->expr()->eq('type', $query->expr()->literal('files'))) - ->andWhere($query->expr()->isNull($query->createFunction('(' . $subQuery->getSql() . ')'))) - ->execute(); - - $this->logger->debug("$deletedEntries orphaned user tag relations deleted", ['app' => 'DeleteOrphanedTagsJob']); - return $deletedEntries; - } - -} diff --git a/apps/files/tests/backgroundjob/DeleteOrphanedItemsJobTest.php b/apps/files/tests/backgroundjob/DeleteOrphanedItemsJobTest.php new file mode 100644 index 00000000000..8d8e5d3be52 --- /dev/null +++ b/apps/files/tests/backgroundjob/DeleteOrphanedItemsJobTest.php @@ -0,0 +1,258 @@ + + * + * @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 OCA\Files\Tests\BackgroundJob; + +use OCA\Files\BackgroundJob\DeleteOrphanedItems; +use OCP\DB\QueryBuilder\IQueryBuilder; + +/** + * Class DeleteOrphanedItemsJobTest + * + * @group DB + * + * @package Test\BackgroundJob + */ +class DeleteOrphanedItemsJobTest extends \Test\TestCase { + + /** @var \OCP\IDBConnection */ + protected $connection; + + protected function setup() { + parent::setUp(); + $this->connection = \OC::$server->getDatabaseConnection(); + } + + protected function cleanMapping($table) { + $query = $this->connection->getQueryBuilder(); + $query->delete($table)->execute(); + } + + protected function getMappings($table) { + $query = $this->connection->getQueryBuilder(); + $query->select('*') + ->from($table); + $result = $query->execute(); + $mapping = $result->fetchAll(); + $result->closeCursor(); + + return $mapping; + } + + /** + * Test clearing orphaned system tag mappings + */ + public function testClearSystemTagMappings() { + $this->cleanMapping('systemtag_object_mapping'); + + $query = $this->connection->getQueryBuilder(); + $query->insert('filecache') + ->values([ + 'storage' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), + 'path' => $query->createNamedParameter('apps/files/tests/deleteorphanedtagsjobtest.php'), + 'path_hash' => $query->createNamedParameter(md5('apps/files/tests/deleteorphanedtagsjobtest.php')), + ])->execute(); + $fileId = $query->getLastInsertId(); + + // Existing file + $query = $this->connection->getQueryBuilder(); + $query->insert('systemtag_object_mapping') + ->values([ + 'objectid' => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT), + 'objecttype' => $query->createNamedParameter('files'), + 'systemtagid' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), + ])->execute(); + + // Non-existing file + $query = $this->connection->getQueryBuilder(); + $query->insert('systemtag_object_mapping') + ->values([ + 'objectid' => $query->createNamedParameter($fileId + 1, IQueryBuilder::PARAM_INT), + 'objecttype' => $query->createNamedParameter('files'), + 'systemtagid' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), + ])->execute(); + + $mapping = $this->getMappings('systemtag_object_mapping'); + $this->assertCount(2, $mapping); + + $job = new DeleteOrphanedItems(); + $this->invokePrivate($job, 'cleanSystemTags'); + + $mapping = $this->getMappings('systemtag_object_mapping'); + $this->assertCount(1, $mapping); + + $query = $this->connection->getQueryBuilder(); + $query->delete('filecache') + ->where($query->expr()->eq('fileid', $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT))) + ->execute(); + $this->cleanMapping('systemtag_object_mapping'); + } + + /** + * Test clearing orphaned system tag mappings + */ + public function testClearUserTagMappings() { + $this->cleanMapping('vcategory_to_object'); + + $query = $this->connection->getQueryBuilder(); + $query->insert('filecache') + ->values([ + 'storage' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), + 'path' => $query->createNamedParameter('apps/files/tests/deleteorphanedtagsjobtest.php'), + 'path_hash' => $query->createNamedParameter(md5('apps/files/tests/deleteorphanedtagsjobtest.php')), + ])->execute(); + $fileId = $query->getLastInsertId(); + + // Existing file + $query = $this->connection->getQueryBuilder(); + $query->insert('vcategory_to_object') + ->values([ + 'objid' => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT), + 'type' => $query->createNamedParameter('files'), + 'categoryid' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), + ])->execute(); + + // Non-existing file + $query = $this->connection->getQueryBuilder(); + $query->insert('vcategory_to_object') + ->values([ + 'objid' => $query->createNamedParameter($fileId + 1, IQueryBuilder::PARAM_INT), + 'type' => $query->createNamedParameter('files'), + 'categoryid' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), + ])->execute(); + + $mapping = $this->getMappings('vcategory_to_object'); + $this->assertCount(2, $mapping); + + $job = new DeleteOrphanedItems(); + $this->invokePrivate($job, 'cleanUserTags'); + + $mapping = $this->getMappings('vcategory_to_object'); + $this->assertCount(1, $mapping); + + $query = $this->connection->getQueryBuilder(); + $query->delete('filecache') + ->where($query->expr()->eq('fileid', $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT))) + ->execute(); + $this->cleanMapping('vcategory_to_object'); + } + + /** + * Test clearing orphaned system tag mappings + */ + public function testClearComments() { + $this->cleanMapping('comments'); + + $query = $this->connection->getQueryBuilder(); + $query->insert('filecache') + ->values([ + 'storage' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), + 'path' => $query->createNamedParameter('apps/files/tests/deleteorphanedtagsjobtest.php'), + 'path_hash' => $query->createNamedParameter(md5('apps/files/tests/deleteorphanedtagsjobtest.php')), + ])->execute(); + $fileId = $query->getLastInsertId(); + + // Existing file + $query = $this->connection->getQueryBuilder(); + $query->insert('comments') + ->values([ + 'object_id' => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT), + 'object_type' => $query->createNamedParameter('files'), + 'actor_id' => $query->createNamedParameter('Alice', IQueryBuilder::PARAM_INT), + 'actor_type' => $query->createNamedParameter('users'), + ])->execute(); + + // Non-existing file + $query = $this->connection->getQueryBuilder(); + $query->insert('comments') + ->values([ + 'object_id' => $query->createNamedParameter($fileId + 1, IQueryBuilder::PARAM_INT), + 'object_type' => $query->createNamedParameter('files'), + 'actor_id' => $query->createNamedParameter('Alice', IQueryBuilder::PARAM_INT), + 'actor_type' => $query->createNamedParameter('users'), + ])->execute(); + + $mapping = $this->getMappings('comments'); + $this->assertCount(2, $mapping); + + $job = new DeleteOrphanedItems(); + $this->invokePrivate($job, 'cleanComments'); + + $mapping = $this->getMappings('comments'); + $this->assertCount(1, $mapping); + + $query = $this->connection->getQueryBuilder(); + $query->delete('filecache') + ->where($query->expr()->eq('fileid', $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT))) + ->execute(); + $this->cleanMapping('comments'); + } + + /** + * Test clearing orphaned system tag mappings + */ + public function testClearCommentReadMarks() { + $this->cleanMapping('comments_read_markers'); + + $query = $this->connection->getQueryBuilder(); + $query->insert('filecache') + ->values([ + 'storage' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), + 'path' => $query->createNamedParameter('apps/files/tests/deleteorphanedtagsjobtest.php'), + 'path_hash' => $query->createNamedParameter(md5('apps/files/tests/deleteorphanedtagsjobtest.php')), + ])->execute(); + $fileId = $query->getLastInsertId(); + + // Existing file + $query = $this->connection->getQueryBuilder(); + $query->insert('comments_read_markers') + ->values([ + 'object_id' => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT), + 'object_type' => $query->createNamedParameter('files'), + 'user_id' => $query->createNamedParameter('Alice', IQueryBuilder::PARAM_INT), + ])->execute(); + + // Non-existing file + $query = $this->connection->getQueryBuilder(); + $query->insert('comments_read_markers') + ->values([ + 'object_id' => $query->createNamedParameter($fileId + 1, IQueryBuilder::PARAM_INT), + 'object_type' => $query->createNamedParameter('files'), + 'user_id' => $query->createNamedParameter('Alice', IQueryBuilder::PARAM_INT), + ])->execute(); + + $mapping = $this->getMappings('comments_read_markers'); + $this->assertCount(2, $mapping); + + $job = new DeleteOrphanedItems(); + $this->invokePrivate($job, 'cleanCommentMarkers'); + + $mapping = $this->getMappings('comments_read_markers'); + $this->assertCount(1, $mapping); + + $query = $this->connection->getQueryBuilder(); + $query->delete('filecache') + ->where($query->expr()->eq('fileid', $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT))) + ->execute(); + $this->cleanMapping('comments_read_markers'); + } + +} diff --git a/apps/files/tests/backgroundjob/DeleteOrphanedTagsJobTest.php b/apps/files/tests/backgroundjob/DeleteOrphanedTagsJobTest.php deleted file mode 100644 index d2e9d77cb20..00000000000 --- a/apps/files/tests/backgroundjob/DeleteOrphanedTagsJobTest.php +++ /dev/null @@ -1,158 +0,0 @@ - - * - * @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 OCA\Files\Tests\BackgroundJob; - -use OCA\Files\BackgroundJob\DeleteOrphanedTagsJob; -use OCP\DB\QueryBuilder\IQueryBuilder; - -/** - * Class DeleteOrphanedTagsJobTest - * - * @group DB - * - * @package Test\BackgroundJob - */ -class DeleteOrphanedTagsJobTest extends \Test\TestCase { - - /** @var \OCP\IDBConnection */ - protected $connection; - - protected function setup() { - parent::setUp(); - $this->connection = \OC::$server->getDatabaseConnection(); - } - - protected function cleanMapping($table) { - $query = $this->connection->getQueryBuilder(); - $query->delete($table)->execute(); - } - - protected function getMappings($table) { - $query = $this->connection->getQueryBuilder(); - $query->select('*') - ->from($table); - $result = $query->execute(); - $mapping = $result->fetchAll(); - $result->closeCursor(); - - return $mapping; - } - - /** - * Test clearing orphaned system tag mappings - */ - public function testClearSystemTagMappings() { - $this->cleanMapping('systemtag_object_mapping'); - - $query = $this->connection->getQueryBuilder(); - $query->insert('filecache') - ->values([ - 'storage' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), - 'path' => $query->createNamedParameter('apps/files/tests/deleteorphanedtagsjobtest.php'), - 'path_hash' => $query->createNamedParameter(md5('apps/files/tests/deleteorphanedtagsjobtest.php')), - ])->execute(); - $fileId = $query->getLastInsertId(); - - // Existing file - $query = $this->connection->getQueryBuilder(); - $query->insert('systemtag_object_mapping') - ->values([ - 'objectid' => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT), - 'objecttype' => $query->createNamedParameter('files'), - 'systemtagid' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), - ])->execute(); - - // Non-existing file - $query = $this->connection->getQueryBuilder(); - $query->insert('systemtag_object_mapping') - ->values([ - 'objectid' => $query->createNamedParameter($fileId + 1, IQueryBuilder::PARAM_INT), - 'objecttype' => $query->createNamedParameter('files'), - 'systemtagid' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), - ])->execute(); - - $mapping = $this->getMappings('systemtag_object_mapping'); - $this->assertCount(2, $mapping); - - $job = new DeleteOrphanedTagsJob(); - $this->invokePrivate($job, 'cleanSystemTags'); - - $mapping = $this->getMappings('systemtag_object_mapping'); - $this->assertCount(1, $mapping); - - $query = $this->connection->getQueryBuilder(); - $query->delete('filecache') - ->where($query->expr()->eq('fileid', $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT))) - ->execute(); - $this->cleanMapping('systemtag_object_mapping'); - } - - /** - * Test clearing orphaned system tag mappings - */ - public function testClearUserTagMappings() { - $this->cleanMapping('vcategory_to_object'); - - $query = $this->connection->getQueryBuilder(); - $query->insert('filecache') - ->values([ - 'storage' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), - 'path' => $query->createNamedParameter('apps/files/tests/deleteorphanedtagsjobtest.php'), - 'path_hash' => $query->createNamedParameter(md5('apps/files/tests/deleteorphanedtagsjobtest.php')), - ])->execute(); - $fileId = $query->getLastInsertId(); - - // Existing file - $query = $this->connection->getQueryBuilder(); - $query->insert('vcategory_to_object') - ->values([ - 'objid' => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT), - 'type' => $query->createNamedParameter('files'), - 'categoryid' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), - ])->execute(); - - // Non-existing file - $query = $this->connection->getQueryBuilder(); - $query->insert('vcategory_to_object') - ->values([ - 'objid' => $query->createNamedParameter($fileId + 1, IQueryBuilder::PARAM_INT), - 'type' => $query->createNamedParameter('files'), - 'categoryid' => $query->createNamedParameter(1337, IQueryBuilder::PARAM_INT), - ])->execute(); - - $mapping = $this->getMappings('vcategory_to_object'); - $this->assertCount(2, $mapping); - - $job = new DeleteOrphanedTagsJob(); - $this->invokePrivate($job, 'cleanUserTags'); - - $mapping = $this->getMappings('vcategory_to_object'); - $this->assertCount(1, $mapping); - - $query = $this->connection->getQueryBuilder(); - $query->delete('filecache') - ->where($query->expr()->eq('fileid', $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT))) - ->execute(); - $this->cleanMapping('vcategory_to_object'); - } - -} -- cgit v1.2.3 From 9d2c433b2466ecd6a012d4103c0637ea0136139b Mon Sep 17 00:00:00 2001 From: Arthur Schiwon Date: Thu, 4 Feb 2016 23:35:19 +0100 Subject: fix pgsql --- apps/files/lib/backgroundjob/deleteorphaneditems.php | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'apps/files') diff --git a/apps/files/lib/backgroundjob/deleteorphaneditems.php b/apps/files/lib/backgroundjob/deleteorphaneditems.php index 37c05ce214f..dc9f781e403 100644 --- a/apps/files/lib/backgroundjob/deleteorphaneditems.php +++ b/apps/files/lib/backgroundjob/deleteorphaneditems.php @@ -110,7 +110,8 @@ class DeleteOrphanedItems extends TimedJob { * @return int Number of deleted entries */ protected function cleanComments() { - $deletedEntries = $this->cleanUp('comments', 'object_id', 'object_type'); + $qb = $this->connection->getQueryBuilder(); + $deletedEntries = $this->cleanUp('comments', $qb->createFunction('CAST(`object_id` as INT)'), 'object_type'); $this->logger->debug("$deletedEntries orphaned comments deleted", ['app' => 'DeleteOrphanedItems']); return $deletedEntries; } @@ -121,7 +122,8 @@ class DeleteOrphanedItems extends TimedJob { * @return int Number of deleted entries */ protected function cleanCommentMarkers() { - $deletedEntries = $this->cleanUp('comments_read_markers', 'object_id', 'object_type'); + $qb = $this->connection->getQueryBuilder(); + $deletedEntries = $this->cleanUp('comments_read_markers', $qb->createFunction('CAST(`object_id` as INT)'), 'object_type'); $this->logger->debug("$deletedEntries orphaned comment read marks deleted", ['app' => 'DeleteOrphanedItems']); return $deletedEntries; } -- cgit v1.2.3 From 065141f6f4212dbbca4633b0138f6e5ac3ec4f7a Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Fri, 5 Feb 2016 15:32:34 +0100 Subject: Move casting to IExpressionBuilder --- .../lib/backgroundjob/deleteorphaneditems.php | 5 +- lib/private/db/querybuilder/expressionbuilder.php | 334 -------------------- .../expressionbuilder/expressionbuilder.php | 351 +++++++++++++++++++++ .../expressionbuilder/ociexpressionbuilder.php | 152 +++++++++ .../expressionbuilder/pgsqlexpressionbuilder.php | 45 +++ .../db/querybuilder/ociexpressionbuilder.php | 135 -------- lib/private/db/querybuilder/querybuilder.php | 6 + lib/public/db/querybuilder/iexpressionbuilder.php | 10 + .../lib/db/querybuilder/expressionbuildertest.php | 2 +- tests/lib/share/share.php | 8 +- 10 files changed, 572 insertions(+), 476 deletions(-) delete mode 100644 lib/private/db/querybuilder/expressionbuilder.php create mode 100644 lib/private/db/querybuilder/expressionbuilder/expressionbuilder.php create mode 100644 lib/private/db/querybuilder/expressionbuilder/ociexpressionbuilder.php create mode 100644 lib/private/db/querybuilder/expressionbuilder/pgsqlexpressionbuilder.php delete mode 100644 lib/private/db/querybuilder/ociexpressionbuilder.php (limited to 'apps/files') diff --git a/apps/files/lib/backgroundjob/deleteorphaneditems.php b/apps/files/lib/backgroundjob/deleteorphaneditems.php index dc9f781e403..cefa1d655de 100644 --- a/apps/files/lib/backgroundjob/deleteorphaneditems.php +++ b/apps/files/lib/backgroundjob/deleteorphaneditems.php @@ -22,6 +22,7 @@ namespace OCA\Files\BackgroundJob; use OC\BackgroundJob\TimedJob; +use OCP\DB\QueryBuilder\IQueryBuilder; /** * Delete all share entries that have no matching entries in the file cache table. @@ -111,7 +112,7 @@ class DeleteOrphanedItems extends TimedJob { */ protected function cleanComments() { $qb = $this->connection->getQueryBuilder(); - $deletedEntries = $this->cleanUp('comments', $qb->createFunction('CAST(`object_id` as INT)'), 'object_type'); + $deletedEntries = $this->cleanUp('comments', $qb->expr()->castColumn('object_id', IQueryBuilder::PARAM_INT), 'object_type'); $this->logger->debug("$deletedEntries orphaned comments deleted", ['app' => 'DeleteOrphanedItems']); return $deletedEntries; } @@ -123,7 +124,7 @@ class DeleteOrphanedItems extends TimedJob { */ protected function cleanCommentMarkers() { $qb = $this->connection->getQueryBuilder(); - $deletedEntries = $this->cleanUp('comments_read_markers', $qb->createFunction('CAST(`object_id` as INT)'), 'object_type'); + $deletedEntries = $this->cleanUp('comments_read_markers', $qb->expr()->castColumn('object_id', IQueryBuilder::PARAM_INT), 'object_type'); $this->logger->debug("$deletedEntries orphaned comment read marks deleted", ['app' => 'DeleteOrphanedItems']); return $deletedEntries; } diff --git a/lib/private/db/querybuilder/expressionbuilder.php b/lib/private/db/querybuilder/expressionbuilder.php deleted file mode 100644 index b688ebfabbe..00000000000 --- a/lib/private/db/querybuilder/expressionbuilder.php +++ /dev/null @@ -1,334 +0,0 @@ - - * - * @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; - -use Doctrine\DBAL\Query\Expression\ExpressionBuilder as DoctrineExpressionBuilder; -use OCP\DB\QueryBuilder\IExpressionBuilder; -use OCP\IDBConnection; - -class ExpressionBuilder implements IExpressionBuilder { - /** @var \Doctrine\DBAL\Query\Expression\ExpressionBuilder */ - protected $expressionBuilder; - - /** @var QuoteHelper */ - protected $helper; - - /** - * Initializes a new ExpressionBuilder. - * - * @param \OCP\IDBConnection $connection - */ - public function __construct(IDBConnection $connection) { - $this->helper = new QuoteHelper(); - $this->expressionBuilder = new DoctrineExpressionBuilder($connection); - } - - /** - * Creates a conjunction of the given boolean expressions. - * - * Example: - * - * [php] - * // (u.type = ?) AND (u.role = ?) - * $expr->andX('u.type = ?', 'u.role = ?')); - * - * @param mixed $x Optional clause. Defaults = null, but requires - * at least one defined when converting to string. - * - * @return \OCP\DB\QueryBuilder\ICompositeExpression - */ - public function andX($x = null) { - $arguments = func_get_args(); - $compositeExpression = call_user_func_array([$this->expressionBuilder, 'andX'], $arguments); - return new CompositeExpression($compositeExpression); - } - - /** - * Creates a disjunction of the given boolean expressions. - * - * Example: - * - * [php] - * // (u.type = ?) OR (u.role = ?) - * $qb->where($qb->expr()->orX('u.type = ?', 'u.role = ?')); - * - * @param mixed $x Optional clause. Defaults = null, but requires - * at least one defined when converting to string. - * - * @return \OCP\DB\QueryBuilder\ICompositeExpression - */ - public function orX($x = null) { - $arguments = func_get_args(); - $compositeExpression = call_user_func_array([$this->expressionBuilder, 'orX'], $arguments); - return new CompositeExpression($compositeExpression); - } - - /** - * Creates a comparison expression. - * - * @param mixed $x The left expression. - * @param string $operator One of the IExpressionBuilder::* constants. - * @param mixed $y The right expression. - * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants - * required when comparing text fields for oci compatibility - * - * @return string - */ - public function comparison($x, $operator, $y, $type = null) { - $x = $this->helper->quoteColumnName($x); - $y = $this->helper->quoteColumnName($y); - return $this->expressionBuilder->comparison($x, $operator, $y); - } - - /** - * Creates an equality comparison expression with the given arguments. - * - * First argument is considered the left expression and the second is the right expression. - * When converted to string, it will generated a = . Example: - * - * [php] - * // u.id = ? - * $expr->eq('u.id', '?'); - * - * @param mixed $x The left expression. - * @param mixed $y The right expression. - * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants - * required when comparing text fields for oci compatibility - * - * @return string - */ - public function eq($x, $y, $type = null) { - $x = $this->helper->quoteColumnName($x); - $y = $this->helper->quoteColumnName($y); - return $this->expressionBuilder->eq($x, $y); - } - - /** - * Creates a non equality comparison expression with the given arguments. - * First argument is considered the left expression and the second is the right expression. - * When converted to string, it will generated a <> . Example: - * - * [php] - * // u.id <> 1 - * $q->where($q->expr()->neq('u.id', '1')); - * - * @param mixed $x The left expression. - * @param mixed $y The right expression. - * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants - * required when comparing text fields for oci compatibility - * - * @return string - */ - public function neq($x, $y, $type = null) { - $x = $this->helper->quoteColumnName($x); - $y = $this->helper->quoteColumnName($y); - return $this->expressionBuilder->neq($x, $y); - } - - /** - * Creates a lower-than comparison expression with the given arguments. - * First argument is considered the left expression and the second is the right expression. - * When converted to string, it will generated a < . Example: - * - * [php] - * // u.id < ? - * $q->where($q->expr()->lt('u.id', '?')); - * - * @param mixed $x The left expression. - * @param mixed $y The right expression. - * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants - * required when comparing text fields for oci compatibility - * - * @return string - */ - public function lt($x, $y, $type = null) { - $x = $this->helper->quoteColumnName($x); - $y = $this->helper->quoteColumnName($y); - return $this->expressionBuilder->lt($x, $y); - } - - /** - * Creates a lower-than-equal comparison expression with the given arguments. - * First argument is considered the left expression and the second is the right expression. - * When converted to string, it will generated a <= . Example: - * - * [php] - * // u.id <= ? - * $q->where($q->expr()->lte('u.id', '?')); - * - * @param mixed $x The left expression. - * @param mixed $y The right expression. - * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants - * required when comparing text fields for oci compatibility - * - * @return string - */ - public function lte($x, $y, $type = null) { - $x = $this->helper->quoteColumnName($x); - $y = $this->helper->quoteColumnName($y); - return $this->expressionBuilder->lte($x, $y); - } - - /** - * Creates a greater-than comparison expression with the given arguments. - * First argument is considered the left expression and the second is the right expression. - * When converted to string, it will generated a > . Example: - * - * [php] - * // u.id > ? - * $q->where($q->expr()->gt('u.id', '?')); - * - * @param mixed $x The left expression. - * @param mixed $y The right expression. - * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants - * required when comparing text fields for oci compatibility - * - * @return string - */ - public function gt($x, $y, $type = null) { - $x = $this->helper->quoteColumnName($x); - $y = $this->helper->quoteColumnName($y); - return $this->expressionBuilder->gt($x, $y); - } - - /** - * Creates a greater-than-equal comparison expression with the given arguments. - * First argument is considered the left expression and the second is the right expression. - * When converted to string, it will generated a >= . Example: - * - * [php] - * // u.id >= ? - * $q->where($q->expr()->gte('u.id', '?')); - * - * @param mixed $x The left expression. - * @param mixed $y The right expression. - * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants - * required when comparing text fields for oci compatibility - * - * @return string - */ - public function gte($x, $y, $type = null) { - $x = $this->helper->quoteColumnName($x); - $y = $this->helper->quoteColumnName($y); - return $this->expressionBuilder->gte($x, $y); - } - - /** - * Creates an IS NULL expression with the given arguments. - * - * @param string $x The field in string format to be restricted by IS NULL. - * - * @return string - */ - public function isNull($x) { - $x = $this->helper->quoteColumnName($x); - return $this->expressionBuilder->isNull($x); - } - - /** - * Creates an IS NOT NULL expression with the given arguments. - * - * @param string $x The field in string format to be restricted by IS NOT NULL. - * - * @return string - */ - public function isNotNull($x) { - $x = $this->helper->quoteColumnName($x); - return $this->expressionBuilder->isNotNull($x); - } - - /** - * Creates a LIKE() comparison expression with the given arguments. - * - * @param string $x Field in string format to be inspected by LIKE() comparison. - * @param mixed $y Argument to be used in LIKE() comparison. - * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants - * required when comparing text fields for oci compatibility - * - * @return string - */ - public function like($x, $y, $type = null) { - $x = $this->helper->quoteColumnName($x); - $y = $this->helper->quoteColumnName($y); - return $this->expressionBuilder->like($x, $y); - } - - /** - * Creates a NOT LIKE() comparison expression with the given arguments. - * - * @param string $x Field in string format to be inspected by NOT LIKE() comparison. - * @param mixed $y Argument to be used in NOT LIKE() comparison. - * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants - * required when comparing text fields for oci compatibility - * - * @return string - */ - public function notLike($x, $y, $type = null) { - $x = $this->helper->quoteColumnName($x); - $y = $this->helper->quoteColumnName($y); - return $this->expressionBuilder->notLike($x, $y); - } - - /** - * Creates a IN () comparison expression with the given arguments. - * - * @param string $x The field in string format to be inspected by IN() comparison. - * @param string|array $y The placeholder or the array of values to be used by IN() comparison. - * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants - * required when comparing text fields for oci compatibility - * - * @return string - */ - public function in($x, $y, $type = null) { - $x = $this->helper->quoteColumnName($x); - $y = $this->helper->quoteColumnNames($y); - return $this->expressionBuilder->in($x, $y); - } - - /** - * Creates a NOT IN () comparison expression with the given arguments. - * - * @param string $x The field in string format to be inspected by NOT IN() comparison. - * @param string|array $y The placeholder or the array of values to be used by NOT IN() comparison. - * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants - * required when comparing text fields for oci compatibility - * - * @return string - */ - public function notIn($x, $y, $type = null) { - $x = $this->helper->quoteColumnName($x); - $y = $this->helper->quoteColumnNames($y); - return $this->expressionBuilder->notIn($x, $y); - } - - /** - * Quotes a given input parameter. - * - * @param mixed $input The parameter to be quoted. - * @param mixed|null $type One of the IQueryBuilder::PARAM_* constants - * - * @return Literal - */ - public function literal($input, $type = null) { - return new Literal($this->expressionBuilder->literal($input, $type)); - } -} diff --git a/lib/private/db/querybuilder/expressionbuilder/expressionbuilder.php b/lib/private/db/querybuilder/expressionbuilder/expressionbuilder.php new file mode 100644 index 00000000000..7ab4c03d97c --- /dev/null +++ b/lib/private/db/querybuilder/expressionbuilder/expressionbuilder.php @@ -0,0 +1,351 @@ + + * + * @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\ExpressionBuilder; + +use Doctrine\DBAL\Query\Expression\ExpressionBuilder as DoctrineExpressionBuilder; +use OC\DB\QueryBuilder\CompositeExpression; +use OC\DB\QueryBuilder\Literal; +use OC\DB\QueryBuilder\QueryFunction; +use OC\DB\QueryBuilder\QuoteHelper; +use OCP\DB\QueryBuilder\IExpressionBuilder; +use OCP\IDBConnection; + +class ExpressionBuilder implements IExpressionBuilder { + /** @var \Doctrine\DBAL\Query\Expression\ExpressionBuilder */ + protected $expressionBuilder; + + /** @var QuoteHelper */ + protected $helper; + + /** + * Initializes a new ExpressionBuilder. + * + * @param \OCP\IDBConnection $connection + */ + public function __construct(IDBConnection $connection) { + $this->helper = new QuoteHelper(); + $this->expressionBuilder = new DoctrineExpressionBuilder($connection); + } + + /** + * Creates a conjunction of the given boolean expressions. + * + * Example: + * + * [php] + * // (u.type = ?) AND (u.role = ?) + * $expr->andX('u.type = ?', 'u.role = ?')); + * + * @param mixed $x Optional clause. Defaults = null, but requires + * at least one defined when converting to string. + * + * @return \OCP\DB\QueryBuilder\ICompositeExpression + */ + public function andX($x = null) { + $arguments = func_get_args(); + $compositeExpression = call_user_func_array([$this->expressionBuilder, 'andX'], $arguments); + return new CompositeExpression($compositeExpression); + } + + /** + * Creates a disjunction of the given boolean expressions. + * + * Example: + * + * [php] + * // (u.type = ?) OR (u.role = ?) + * $qb->where($qb->expr()->orX('u.type = ?', 'u.role = ?')); + * + * @param mixed $x Optional clause. Defaults = null, but requires + * at least one defined when converting to string. + * + * @return \OCP\DB\QueryBuilder\ICompositeExpression + */ + public function orX($x = null) { + $arguments = func_get_args(); + $compositeExpression = call_user_func_array([$this->expressionBuilder, 'orX'], $arguments); + return new CompositeExpression($compositeExpression); + } + + /** + * Creates a comparison expression. + * + * @param mixed $x The left expression. + * @param string $operator One of the IExpressionBuilder::* constants. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function comparison($x, $operator, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->comparison($x, $operator, $y); + } + + /** + * Creates an equality comparison expression with the given arguments. + * + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a = . Example: + * + * [php] + * // u.id = ? + * $expr->eq('u.id', '?'); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function eq($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->eq($x, $y); + } + + /** + * Creates a non equality comparison expression with the given arguments. + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a <> . Example: + * + * [php] + * // u.id <> 1 + * $q->where($q->expr()->neq('u.id', '1')); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function neq($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->neq($x, $y); + } + + /** + * Creates a lower-than comparison expression with the given arguments. + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a < . Example: + * + * [php] + * // u.id < ? + * $q->where($q->expr()->lt('u.id', '?')); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function lt($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->lt($x, $y); + } + + /** + * Creates a lower-than-equal comparison expression with the given arguments. + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a <= . Example: + * + * [php] + * // u.id <= ? + * $q->where($q->expr()->lte('u.id', '?')); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function lte($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->lte($x, $y); + } + + /** + * Creates a greater-than comparison expression with the given arguments. + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a > . Example: + * + * [php] + * // u.id > ? + * $q->where($q->expr()->gt('u.id', '?')); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function gt($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->gt($x, $y); + } + + /** + * Creates a greater-than-equal comparison expression with the given arguments. + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a >= . Example: + * + * [php] + * // u.id >= ? + * $q->where($q->expr()->gte('u.id', '?')); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function gte($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->gte($x, $y); + } + + /** + * Creates an IS NULL expression with the given arguments. + * + * @param string $x The field in string format to be restricted by IS NULL. + * + * @return string + */ + public function isNull($x) { + $x = $this->helper->quoteColumnName($x); + return $this->expressionBuilder->isNull($x); + } + + /** + * Creates an IS NOT NULL expression with the given arguments. + * + * @param string $x The field in string format to be restricted by IS NOT NULL. + * + * @return string + */ + public function isNotNull($x) { + $x = $this->helper->quoteColumnName($x); + return $this->expressionBuilder->isNotNull($x); + } + + /** + * Creates a LIKE() comparison expression with the given arguments. + * + * @param string $x Field in string format to be inspected by LIKE() comparison. + * @param mixed $y Argument to be used in LIKE() comparison. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function like($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->like($x, $y); + } + + /** + * Creates a NOT LIKE() comparison expression with the given arguments. + * + * @param string $x Field in string format to be inspected by NOT LIKE() comparison. + * @param mixed $y Argument to be used in NOT LIKE() comparison. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function notLike($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->notLike($x, $y); + } + + /** + * Creates a IN () comparison expression with the given arguments. + * + * @param string $x The field in string format to be inspected by IN() comparison. + * @param string|array $y The placeholder or the array of values to be used by IN() comparison. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function in($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnNames($y); + return $this->expressionBuilder->in($x, $y); + } + + /** + * Creates a NOT IN () comparison expression with the given arguments. + * + * @param string $x The field in string format to be inspected by NOT IN() comparison. + * @param string|array $y The placeholder or the array of values to be used by NOT IN() comparison. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function notIn($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnNames($y); + return $this->expressionBuilder->notIn($x, $y); + } + + /** + * Quotes a given input parameter. + * + * @param mixed $input The parameter to be quoted. + * @param mixed|null $type One of the IQueryBuilder::PARAM_* constants + * + * @return Literal + */ + public function literal($input, $type = null) { + return new Literal($this->expressionBuilder->literal($input, $type)); + } + + /** + * Returns a IQueryFunction that casts the column to the given type + * + * @param string $column + * @param mixed $type One of IQueryBuilder::PARAM_* + * @return string + */ + public function castColumn($column, $type) { + return new QueryFunction( + $this->helper->quoteColumnName($column) + ); + } +} diff --git a/lib/private/db/querybuilder/expressionbuilder/ociexpressionbuilder.php b/lib/private/db/querybuilder/expressionbuilder/ociexpressionbuilder.php new file mode 100644 index 00000000000..6a6d0f455f6 --- /dev/null +++ b/lib/private/db/querybuilder/expressionbuilder/ociexpressionbuilder.php @@ -0,0 +1,152 @@ + + * + * @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\ExpressionBuilder; + + +use OC\DB\QueryBuilder\QueryFunction; +use OCP\DB\QueryBuilder\ILiteral; +use OCP\DB\QueryBuilder\IParameter; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\DB\QueryBuilder\IQueryFunction; + +class OCIExpressionBuilder extends ExpressionBuilder { + + /** + * @param mixed $column + * @param mixed|null $type + * @return array|IQueryFunction|string + */ + protected function prepareColumn($column, $type) { + if ($type === IQueryBuilder::PARAM_STR && !is_array($column) && !($column instanceof IParameter) && !($column instanceof ILiteral)) { + $column = $this->castColumn($column, $type); + } else { + $column = $this->helper->quoteColumnNames($column); + } + return $column; + } + + /** + * @inheritdoc + */ + public function comparison($x, $operator, $y, $type = null) { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + + return $this->expressionBuilder->comparison($x, $operator, $y); + } + + /** + * @inheritdoc + */ + public function eq($x, $y, $type = null) { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + + return $this->expressionBuilder->eq($x, $y); + } + + /** + * @inheritdoc + */ + public function neq($x, $y, $type = null) { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + + return $this->expressionBuilder->neq($x, $y); + } + + /** + * @inheritdoc + */ + public function lt($x, $y, $type = null) { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + + return $this->expressionBuilder->lt($x, $y); + } + + /** + * @inheritdoc + */ + public function lte($x, $y, $type = null) { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + + return $this->expressionBuilder->lte($x, $y); + } + + /** + * @inheritdoc + */ + public function gt($x, $y, $type = null) { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + + return $this->expressionBuilder->gt($x, $y); + } + + /** + * @inheritdoc + */ + public function gte($x, $y, $type = null) { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + + return $this->expressionBuilder->gte($x, $y); + } + + /** + * @inheritdoc + */ + public function in($x, $y, $type = null) { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + + return $this->expressionBuilder->in($x, $y); + } + + /** + * @inheritdoc + */ + public function notIn($x, $y, $type = null) { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + + return $this->expressionBuilder->notIn($x, $y); + } + + /** + * Returns a IQueryFunction that casts the column to the given type + * + * @param string $column + * @param mixed $type One of IQueryBuilder::PARAM_* + * @return IQueryFunction + */ + public function castColumn($column, $type) { + if ($type === IQueryBuilder::PARAM_STR) { + $column = $this->helper->quoteColumnName($column); + return new QueryFunction('to_char(' . $column . ')'); + } + + return parent::castColumn($column, $type); + } +} diff --git a/lib/private/db/querybuilder/expressionbuilder/pgsqlexpressionbuilder.php b/lib/private/db/querybuilder/expressionbuilder/pgsqlexpressionbuilder.php new file mode 100644 index 00000000000..8a0b68db998 --- /dev/null +++ b/lib/private/db/querybuilder/expressionbuilder/pgsqlexpressionbuilder.php @@ -0,0 +1,45 @@ + + * + * @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\ExpressionBuilder; + + +use OC\DB\QueryBuilder\QueryFunction; +use OCP\DB\QueryBuilder\IQueryBuilder; + +class PgSqlExpressionBuilder extends ExpressionBuilder { + + /** + * Returns a IQueryFunction that casts the column to the given type + * + * @param string $column + * @param mixed $type One of IQueryBuilder::PARAM_* + * @return string + */ + public function castColumn($column, $type) { + if ($type === IQueryBuilder::PARAM_INT) { + $column = $this->helper->quoteColumnName($column); + return new QueryFunction('CAST(' . $column . ' AS INT)'); + } + + return parent::castColumn($column, $type); + } +} diff --git a/lib/private/db/querybuilder/ociexpressionbuilder.php b/lib/private/db/querybuilder/ociexpressionbuilder.php deleted file mode 100644 index 4c127bd752d..00000000000 --- a/lib/private/db/querybuilder/ociexpressionbuilder.php +++ /dev/null @@ -1,135 +0,0 @@ - - * - * @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; - - -use OCP\DB\QueryBuilder\ILiteral; -use OCP\DB\QueryBuilder\IParameter; -use OCP\DB\QueryBuilder\IQueryBuilder; - -class OCIExpressionBuilder extends ExpressionBuilder { - - /** - * @param mixed $column - * @param mixed|null $type - * @return array|QueryFunction|string - */ - protected function prepareColumn($column, $type) { - if ($type === IQueryBuilder::PARAM_STR && !is_array($column) && !($column instanceof IParameter) && !($column instanceof ILiteral)) { - $column = $this->helper->quoteColumnName($column); - $column = new QueryFunction('to_char(' . $column . ')'); - } else { - $column = $this->helper->quoteColumnNames($column); - } - return $column; - } - - /** - * @inheritdoc - */ - public function comparison($x, $operator, $y, $type = null) { - $x = $this->prepareColumn($x, $type); - $y = $this->prepareColumn($y, $type); - - return $this->expressionBuilder->comparison($x, $operator, $y); - } - - /** - * @inheritdoc - */ - public function eq($x, $y, $type = null) { - $x = $this->prepareColumn($x, $type); - $y = $this->prepareColumn($y, $type); - - return $this->expressionBuilder->eq($x, $y); - } - - /** - * @inheritdoc - */ - public function neq($x, $y, $type = null) { - $x = $this->prepareColumn($x, $type); - $y = $this->prepareColumn($y, $type); - - return $this->expressionBuilder->neq($x, $y); - } - - /** - * @inheritdoc - */ - public function lt($x, $y, $type = null) { - $x = $this->prepareColumn($x, $type); - $y = $this->prepareColumn($y, $type); - - return $this->expressionBuilder->lt($x, $y); - } - - /** - * @inheritdoc - */ - public function lte($x, $y, $type = null) { - $x = $this->prepareColumn($x, $type); - $y = $this->prepareColumn($y, $type); - - return $this->expressionBuilder->lte($x, $y); - } - - /** - * @inheritdoc - */ - public function gt($x, $y, $type = null) { - $x = $this->prepareColumn($x, $type); - $y = $this->prepareColumn($y, $type); - - return $this->expressionBuilder->gt($x, $y); - } - - /** - * @inheritdoc - */ - public function gte($x, $y, $type = null) { - $x = $this->prepareColumn($x, $type); - $y = $this->prepareColumn($y, $type); - - return $this->expressionBuilder->gte($x, $y); - } - - /** - * @inheritdoc - */ - public function in($x, $y, $type = null) { - $x = $this->prepareColumn($x, $type); - $y = $this->prepareColumn($y, $type); - - return $this->expressionBuilder->in($x, $y); - } - - /** - * @inheritdoc - */ - public function notIn($x, $y, $type = null) { - $x = $this->prepareColumn($x, $type); - $y = $this->prepareColumn($y, $type); - - return $this->expressionBuilder->notIn($x, $y); - } -} diff --git a/lib/private/db/querybuilder/querybuilder.php b/lib/private/db/querybuilder/querybuilder.php index 42b290b90e7..ff31ffbc043 100644 --- a/lib/private/db/querybuilder/querybuilder.php +++ b/lib/private/db/querybuilder/querybuilder.php @@ -21,7 +21,11 @@ namespace OC\DB\QueryBuilder; +use Doctrine\DBAL\Platforms\PostgreSqlPlatform; use OC\DB\OracleConnection; +use OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder; +use OC\DB\QueryBuilder\ExpressionBuilder\OCIExpressionBuilder; +use OC\DB\QueryBuilder\ExpressionBuilder\PgSqlExpressionBuilder; use OCP\DB\QueryBuilder\IQueryBuilder; use OCP\DB\QueryBuilder\IQueryFunction; use OCP\DB\QueryBuilder\IParameter; @@ -85,6 +89,8 @@ class QueryBuilder implements IQueryBuilder { public function expr() { if ($this->connection instanceof OracleConnection) { return new OCIExpressionBuilder($this->connection); + } else if ($this->connection->getDatabasePlatform() instanceof PostgreSqlPlatform) { + return new PgSqlExpressionBuilder($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 a53ae3846c2..4b53a0e0b8b 100644 --- a/lib/public/db/querybuilder/iexpressionbuilder.php +++ b/lib/public/db/querybuilder/iexpressionbuilder.php @@ -299,4 +299,14 @@ interface IExpressionBuilder { * @since 8.2.0 */ public function literal($input, $type = null); + + /** + * Returns a IQueryFunction that casts the column to the given type + * + * @param string $column + * @param mixed $type One of IQueryBuilder::PARAM_* + * @return string + * @since 9.0.0 + */ + public function castColumn($column, $type); } diff --git a/tests/lib/db/querybuilder/expressionbuildertest.php b/tests/lib/db/querybuilder/expressionbuildertest.php index 8310c4adf68..f38faab9169 100644 --- a/tests/lib/db/querybuilder/expressionbuildertest.php +++ b/tests/lib/db/querybuilder/expressionbuildertest.php @@ -22,7 +22,7 @@ namespace Test\DB\QueryBuilder; use Doctrine\DBAL\Query\Expression\ExpressionBuilder as DoctrineExpressionBuilder; -use OC\DB\QueryBuilder\ExpressionBuilder; +use OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder; use OCP\DB\QueryBuilder\IQueryBuilder; use Test\TestCase; diff --git a/tests/lib/share/share.php b/tests/lib/share/share.php index 5162a03f367..4519c33f9d1 100644 --- a/tests/lib/share/share.php +++ b/tests/lib/share/share.php @@ -1423,7 +1423,7 @@ class Test_Share extends \Test\TestCase { $userSession->method('getUser')->willReturn($user); - $ex = $this->getMockBuilder('\OC\DB\QueryBuilder\ExpressionBuilder') + $ex = $this->getMockBuilder('\OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder') ->disableOriginalConstructor() ->getMock(); $qb = $this->getMockBuilder('\OC\DB\QueryBuilder\QueryBuilder') @@ -1478,7 +1478,7 @@ class Test_Share extends \Test\TestCase { $userSession->method('getUser')->willReturn($user); - $ex = $this->getMockBuilder('\OC\DB\QueryBuilder\ExpressionBuilder') + $ex = $this->getMockBuilder('\OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder') ->disableOriginalConstructor() ->getMock(); $qb = $this->getMockBuilder('\OC\DB\QueryBuilder\QueryBuilder') @@ -1531,7 +1531,7 @@ class Test_Share extends \Test\TestCase { $userSession->method('getUser')->willReturn($user); - $ex = $this->getMockBuilder('\OC\DB\QueryBuilder\ExpressionBuilder') + $ex = $this->getMockBuilder('\OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder') ->disableOriginalConstructor() ->getMock(); $qb = $this->getMockBuilder('\OC\DB\QueryBuilder\QueryBuilder') @@ -1584,7 +1584,7 @@ class Test_Share extends \Test\TestCase { $userSession->method('getUser')->willReturn($user); - $ex = $this->getMockBuilder('\OC\DB\QueryBuilder\ExpressionBuilder') + $ex = $this->getMockBuilder('\OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder') ->disableOriginalConstructor() ->getMock(); $qb = $this->getMockBuilder('\OC\DB\QueryBuilder\QueryBuilder') -- cgit v1.2.3