From f2c7acb3c065c35a1e75d512d0ce193f1989296f Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Tue, 8 Dec 2015 09:40:20 +0100 Subject: Allow getting the last insert id without much hassle --- lib/private/db/querybuilder/querybuilder.php | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'lib/private') diff --git a/lib/private/db/querybuilder/querybuilder.php b/lib/private/db/querybuilder/querybuilder.php index 02d8ee4344d..e70733b5509 100644 --- a/lib/private/db/querybuilder/querybuilder.php +++ b/lib/private/db/querybuilder/querybuilder.php @@ -1023,6 +1023,21 @@ class QueryBuilder implements IQueryBuilder { return new QueryFunction($call); } + /** + * Used to get the id of the last inserted element + * @return int + * @throws \BadMethodCallException When being called before an insert query has been run. + */ + public function getLastInsertId() { + $from = $this->getQueryPart('from'); + + if ($this->getType() === \Doctrine\DBAL\Query\QueryBuilder::INSERT && !empty($from)) { + return (int) $this->connection->lastInsertId($from['table']); + } + + throw new \BadMethodCallException('Invalid call to getLastInsertId without using insert() before.'); + } + /** * @param string $table * @return string -- cgit v1.2.3 From a3391248e46bbc389dc1880f7ae50aa5dade8731 Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Tue, 8 Dec 2015 09:49:21 +0100 Subject: Add select distinct to the query builder --- lib/private/db/querybuilder/querybuilder.php | 22 ++++++++++++++++ lib/public/db/querybuilder/iquerybuilder.php | 16 ++++++++++++ tests/lib/db/querybuilder/querybuildertest.php | 36 +++++++++++++++++++++++--- 3 files changed, 70 insertions(+), 4 deletions(-) (limited to 'lib/private') diff --git a/lib/private/db/querybuilder/querybuilder.php b/lib/private/db/querybuilder/querybuilder.php index e70733b5509..b874f5c9911 100644 --- a/lib/private/db/querybuilder/querybuilder.php +++ b/lib/private/db/querybuilder/querybuilder.php @@ -324,6 +324,28 @@ class QueryBuilder implements IQueryBuilder { return $this; } + /** + * Specifies an item that is to be returned uniquely in the query result. + * + * + * $qb = $conn->getQueryBuilder() + * ->selectDistinct('type') + * ->from('users'); + * + * + * @param mixed $select The selection expressions. + * + * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance. + */ + public function selectDistinct($select) { + + $this->queryBuilder->addSelect( + 'DISTINCT ' . $this->helper->quoteColumnName($select) + ); + + return $this; + } + /** * Adds an item that is to be returned in the query result. * diff --git a/lib/public/db/querybuilder/iquerybuilder.php b/lib/public/db/querybuilder/iquerybuilder.php index e3105cf134e..1ff1077d53f 100644 --- a/lib/public/db/querybuilder/iquerybuilder.php +++ b/lib/public/db/querybuilder/iquerybuilder.php @@ -256,6 +256,22 @@ interface IQueryBuilder { */ public function selectAlias($select, $alias); + /** + * Specifies an item that is to be returned uniquely in the query result. + * + * + * $qb = $conn->getQueryBuilder() + * ->selectDistinct('type') + * ->from('users'); + * + * + * @param mixed $select The selection expressions. + * + * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance. + * @since 9.0.0 + */ + public function selectDistinct($select); + /** * Adds an item that is to be returned in the query result. * diff --git a/tests/lib/db/querybuilder/querybuildertest.php b/tests/lib/db/querybuilder/querybuildertest.php index 828a860ee80..b52fbd7c283 100644 --- a/tests/lib/db/querybuilder/querybuildertest.php +++ b/tests/lib/db/querybuilder/querybuildertest.php @@ -48,12 +48,12 @@ class QueryBuilderTest extends \Test\TestCase { $this->queryBuilder = new QueryBuilder($this->connection); } - protected function createTestingRows() { + protected function createTestingRows($appId = 'testFirstResult') { $qB = $this->connection->getQueryBuilder(); for ($i = 1; $i < 10; $i++) { $qB->insert('*PREFIX*appconfig') ->values([ - 'appid' => $qB->expr()->literal('testFirstResult'), + 'appid' => $qB->expr()->literal($appId), 'configkey' => $qB->expr()->literal('testing' . $i), 'configvalue' => $qB->expr()->literal(100 - $i), ]) @@ -80,11 +80,11 @@ class QueryBuilderTest extends \Test\TestCase { return $rows; } - protected function deleteTestingRows() { + protected function deleteTestingRows($appId = 'testFirstResult') { $qB = $this->connection->getQueryBuilder(); $qB->delete('*PREFIX*appconfig') - ->where($qB->expr()->eq('appid', $qB->expr()->literal('testFirstResult'))) + ->where($qB->expr()->eq('appid', $qB->expr()->literal($appId))) ->execute(); } @@ -272,6 +272,34 @@ class QueryBuilderTest extends \Test\TestCase { $this->deleteTestingRows(); } + public function testSelectDistinct() { + $this->deleteTestingRows('testFirstResult1'); + $this->deleteTestingRows('testFirstResult2'); + $this->createTestingRows('testFirstResult1'); + $this->createTestingRows('testFirstResult2'); + + $this->queryBuilder->selectDistinct('appid'); + + $this->queryBuilder->from('*PREFIX*appconfig') + ->where($this->queryBuilder->expr()->in( + 'appid', + [$this->queryBuilder->expr()->literal('testFirstResult1'), $this->queryBuilder->expr()->literal('testFirstResult2')] + )) + ->orderBy('appid', 'DESC'); + + $query = $this->queryBuilder->execute(); + $rows = $query->fetchAll(); + $query->closeCursor(); + + $this->assertEquals( + [['appid' => 'testFirstResult2'], ['appid' => 'testFirstResult1']], + $rows + ); + + $this->deleteTestingRows('testFirstResult1'); + $this->deleteTestingRows('testFirstResult2'); + } + public function dataAddSelect() { $queryBuilder = new QueryBuilder(\OC::$server->getDatabaseConnection()); return [ -- cgit v1.2.3 From 9f9884930609addad3e37325731fb5406dd3aa44 Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Tue, 8 Dec 2015 09:57:38 +0100 Subject: Add a method to the get "to use" table and column name --- lib/private/db/querybuilder/querybuilder.php | 19 ++++++++++++++++++- lib/private/db/querybuilder/quotehelper.php | 2 +- lib/public/db/querybuilder/iquerybuilder.php | 19 +++++++++++++++++++ tests/lib/db/querybuilder/querybuildertest.php | 22 +++++++++++++++++++++- 4 files changed, 59 insertions(+), 3 deletions(-) (limited to 'lib/private') diff --git a/lib/private/db/querybuilder/querybuilder.php b/lib/private/db/querybuilder/querybuilder.php index b874f5c9911..741da4efc27 100644 --- a/lib/private/db/querybuilder/querybuilder.php +++ b/lib/private/db/querybuilder/querybuilder.php @@ -1061,14 +1061,31 @@ class QueryBuilder implements IQueryBuilder { } /** + * Returns the table name quoted and with database prefix as needed by the implementation + * * @param string $table * @return string */ - private function getTableName($table) { + public function getTableName($table) { if ($this->automaticTablePrefix === false || strpos($table, '*PREFIX*') === 0) { return $this->helper->quoteColumnName($table); } return $this->helper->quoteColumnName('*PREFIX*' . $table); } + + /** + * Returns the column name quoted and with table alias prefix as needed by the implementation + * + * @param string $column + * @param string $tableAlias + * @return string + */ + public function getColumnName($column, $tableAlias = '') { + if ($tableAlias !== '') { + $tableAlias .= '.'; + } + + return $this->helper->quoteColumnName($tableAlias . $column); + } } diff --git a/lib/private/db/querybuilder/quotehelper.php b/lib/private/db/querybuilder/quotehelper.php index 4b62fee6a6c..5ceb76bbf93 100644 --- a/lib/private/db/querybuilder/quotehelper.php +++ b/lib/private/db/querybuilder/quotehelper.php @@ -61,7 +61,7 @@ class QuoteHelper { } if (substr_count($string, '.')) { - list($alias, $columnName) = explode('.', $string); + list($alias, $columnName) = explode('.', $string, 2); if ($columnName === '*') { return $string; diff --git a/lib/public/db/querybuilder/iquerybuilder.php b/lib/public/db/querybuilder/iquerybuilder.php index 1ff1077d53f..dd3ee7da5f5 100644 --- a/lib/public/db/querybuilder/iquerybuilder.php +++ b/lib/public/db/querybuilder/iquerybuilder.php @@ -820,4 +820,23 @@ interface IQueryBuilder { * @since 9.0.0 */ public function getLastInsertId(); + + /** + * Returns the table name quoted and with database prefix as needed by the implementation + * + * @param string $table + * @return string + * @since 9.0.0 + */ + public function getTableName($table); + + /** + * Returns the column name quoted and with table alias prefix as needed by the implementation + * + * @param string $column + * @param string $tableAlias + * @return string + * @since 9.0.0 + */ + public function getColumnName($column, $tableAlias = ''); } diff --git a/tests/lib/db/querybuilder/querybuildertest.php b/tests/lib/db/querybuilder/querybuildertest.php index b52fbd7c283..c8e029d9e40 100644 --- a/tests/lib/db/querybuilder/querybuildertest.php +++ b/tests/lib/db/querybuilder/querybuildertest.php @@ -1165,7 +1165,27 @@ class QueryBuilderTest extends \Test\TestCase { $this->assertSame( $expected, - $this->invokePrivate($this->queryBuilder, 'getTableName', [$tableName]) + $this->queryBuilder->getTableName($tableName) + ); + } + + public function dataGetColumnName() { + return [ + ['column', '', '`column`'], + ['column', 'a', 'a.`column`'], + ]; + } + + /** + * @dataProvider dataGetColumnName + * @param string $column + * @param string $prefix + * @param string $expected + */ + public function testGetColumnName($column, $prefix, $expected) { + $this->assertSame( + $expected, + $this->queryBuilder->getColumnName($column, $prefix) ); } } -- cgit v1.2.3