diff options
Diffstat (limited to 'tests/lib/DB')
| -rw-r--r-- | tests/lib/DB/ConnectionTest.php | 200 | ||||
| -rw-r--r-- | tests/lib/DB/DBSchemaTest.php | 107 | ||||
| -rw-r--r-- | tests/lib/DB/LegacyDBTest.php | 393 | ||||
| -rw-r--r-- | tests/lib/DB/MDB2SchemaManagerTest.php | 52 | ||||
| -rw-r--r-- | tests/lib/DB/MDB2SchemaReaderTest.php | 83 | ||||
| -rw-r--r-- | tests/lib/DB/MigratorTest.php | 203 | ||||
| -rw-r--r-- | tests/lib/DB/MySqlMigrationTest.php | 49 | ||||
| -rw-r--r-- | tests/lib/DB/QueryBuilder/ExpressionBuilderTest.php | 427 | ||||
| -rw-r--r-- | tests/lib/DB/QueryBuilder/QueryBuilderTest.php | 1203 | ||||
| -rw-r--r-- | tests/lib/DB/QueryBuilder/QuoteHelperTest.php | 145 | ||||
| -rw-r--r-- | tests/lib/DB/SqliteMigrationTest.php | 49 | ||||
| -rw-r--r-- | tests/lib/DB/testschema.xml | 83 | ||||
| -rw-r--r-- | tests/lib/DB/ts-autoincrement-after.xml | 32 | ||||
| -rw-r--r-- | tests/lib/DB/ts-autoincrement-before.xml | 24 |
14 files changed, 3050 insertions, 0 deletions
diff --git a/tests/lib/DB/ConnectionTest.php b/tests/lib/DB/ConnectionTest.php new file mode 100644 index 00000000000..b9c55c23339 --- /dev/null +++ b/tests/lib/DB/ConnectionTest.php @@ -0,0 +1,200 @@ +<?php + +/** + * Copyright (c) 2014 Robin Appelman <icewind@owncloud.com> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + +namespace Test\DB; + +use Doctrine\DBAL\Platforms\SqlitePlatform; +use OC\DB\MDB2SchemaManager; +use OCP\DB\QueryBuilder\IQueryBuilder; + +/** + * Class Connection + * + * @group DB + * + * @package Test\DB + */ +class ConnectionTest extends \Test\TestCase { + /** + * @var \OCP\IDBConnection + */ + private $connection; + + public static function setUpBeforeClass() { + self::dropTestTable(); + parent::setUpBeforeClass(); + } + + public static function tearDownAfterClass() { + self::dropTestTable(); + parent::tearDownAfterClass(); + } + + protected static function dropTestTable() { + if (\OC::$server->getConfig()->getSystemValue('dbtype', 'sqlite') !== 'oci') { + \OC::$server->getDatabaseConnection()->dropTable('table'); + } + } + + public function setUp() { + parent::setUp(); + $this->connection = \OC::$server->getDatabaseConnection(); + } + + public function tearDown() { + parent::tearDown(); + $this->connection->dropTable('table'); + } + + /** + * @param string $table + */ + public function assertTableExist($table) { + if ($this->connection->getDatabasePlatform() instanceof SqlitePlatform) { + // sqlite removes the tables after closing the DB + $this->assertTrue(true); + } else { + $this->assertTrue($this->connection->tableExists($table), 'Table ' . $table . ' exists.'); + } + } + + /** + * @param string $table + */ + public function assertTableNotExist($table) { + if ($this->connection->getDatabasePlatform() instanceof SqlitePlatform) { + // sqlite removes the tables after closing the DB + $this->assertTrue(true); + } else { + $this->assertFalse($this->connection->tableExists($table), 'Table ' . $table . " doesn't exist."); + } + } + + private function makeTestTable() { + $schemaManager = new MDB2SchemaManager($this->connection); + $schemaManager->createDbFromStructure(__DIR__ . '/testschema.xml'); + } + + public function testTableExists() { + $this->assertTableNotExist('table'); + $this->makeTestTable(); + $this->assertTableExist('table'); + } + + /** + * @depends testTableExists + */ + public function testDropTable() { + $this->makeTestTable(); + $this->assertTableExist('table'); + $this->connection->dropTable('table'); + $this->assertTableNotExist('table'); + } + + private function getTextValueByIntergerField($integerField) { + $builder = $this->connection->getQueryBuilder(); + $query = $builder->select('textfield') + ->from('table') + ->where($builder->expr()->eq('integerfield', $builder->createNamedParameter($integerField, IQueryBuilder::PARAM_INT))); + + $result = $query->execute(); + return $result->fetchColumn(); + } + + public function testSetValues() { + $this->makeTestTable(); + $this->connection->setValues('table', [ + 'integerfield' => 1 + ], [ + 'textfield' => 'foo', + 'clobfield' => 'not_null' + ]); + + $this->assertEquals('foo', $this->getTextValueByIntergerField(1)); + } + + public function testSetValuesOverWrite() { + $this->makeTestTable(); + $this->connection->setValues('table', [ + 'integerfield' => 1 + ], [ + 'textfield' => 'foo', + 'clobfield' => 'not_null' + ]); + + $this->connection->setValues('table', [ + 'integerfield' => 1 + ], [ + 'textfield' => 'bar' + ]); + + $this->assertEquals('bar', $this->getTextValueByIntergerField(1)); + } + + public function testSetValuesOverWritePrecondition() { + $this->makeTestTable(); + $this->connection->setValues('table', [ + 'integerfield' => 1 + ], [ + 'textfield' => 'foo', + 'booleanfield' => true, + 'clobfield' => 'not_null' + ]); + + $this->connection->setValues('table', [ + 'integerfield' => 1 + ], [ + 'textfield' => 'bar' + ], [ + 'booleanfield' => true + ]); + + $this->assertEquals('bar', $this->getTextValueByIntergerField(1)); + } + + /** + * @expectedException \OCP\PreConditionNotMetException + */ + public function testSetValuesOverWritePreconditionFailed() { + $this->makeTestTable(); + $this->connection->setValues('table', [ + 'integerfield' => 1 + ], [ + 'textfield' => 'foo', + 'booleanfield' => true, + 'clobfield' => 'not_null' + ]); + + $this->connection->setValues('table', [ + 'integerfield' => 1 + ], [ + 'textfield' => 'bar' + ], [ + 'booleanfield' => false + ]); + } + + public function testSetValuesSameNoError() { + $this->makeTestTable(); + $this->connection->setValues('table', [ + 'integerfield' => 1 + ], [ + 'textfield' => 'foo', + 'clobfield' => 'not_null' + ]); + + // this will result in 'no affected rows' on certain optimizing DBs + // ensure the PreConditionNotMetException isn't thrown + $this->connection->setValues('table', [ + 'integerfield' => 1 + ], [ + 'textfield' => 'foo' + ]); + } +} diff --git a/tests/lib/DB/DBSchemaTest.php b/tests/lib/DB/DBSchemaTest.php new file mode 100644 index 00000000000..284fc532c2a --- /dev/null +++ b/tests/lib/DB/DBSchemaTest.php @@ -0,0 +1,107 @@ +<?php +/** + * Copyright (c) 2012 Bart Visscher <bartv@thisnet.nl> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + +namespace Test\DB; + +use OC_DB; +use OCP\Security\ISecureRandom; + +/** + * Class DBSchemaTest + * + * @group DB + */ +class DBSchemaTest extends \Test\TestCase { + protected $schema_file = 'static://test_db_scheme'; + protected $schema_file2 = 'static://test_db_scheme2'; + protected $table1; + protected $table2; + + protected function setUp() { + parent::setUp(); + + $dbfile = \OC::$SERVERROOT.'/tests/data/db_structure.xml'; + $dbfile2 = \OC::$SERVERROOT.'/tests/data/db_structure2.xml'; + + $r = '_' . \OC::$server->getSecureRandom()-> + generate(4, ISecureRandom::CHAR_LOWER . ISecureRandom::CHAR_DIGITS) . '_'; + $content = file_get_contents( $dbfile ); + $content = str_replace( '*dbprefix*', '*dbprefix*'.$r, $content ); + file_put_contents( $this->schema_file, $content ); + $content = file_get_contents( $dbfile2 ); + $content = str_replace( '*dbprefix*', '*dbprefix*'.$r, $content ); + file_put_contents( $this->schema_file2, $content ); + + $this->table1 = $r.'cntcts_addrsbks'; + $this->table2 = $r.'cntcts_cards'; + } + + protected function tearDown() { + unlink($this->schema_file); + unlink($this->schema_file2); + + parent::tearDown(); + } + + // everything in one test, they depend on each other + /** + * @medium + */ + public function testSchema() { + $platform = \OC::$server->getDatabaseConnection()->getDatabasePlatform(); + $this->doTestSchemaCreating(); + $this->doTestSchemaChanging(); + $this->doTestSchemaDumping(); + $this->doTestSchemaRemoving(); + } + + public function doTestSchemaCreating() { + OC_DB::createDbFromStructure($this->schema_file); + $this->assertTableExist($this->table1); + $this->assertTableExist($this->table2); + } + + public function doTestSchemaChanging() { + OC_DB::updateDbFromStructure($this->schema_file2); + $this->assertTableExist($this->table2); + } + + public function doTestSchemaDumping() { + $outfile = 'static://db_out.xml'; + OC_DB::getDbStructure($outfile); + $content = file_get_contents($outfile); + $this->assertContains($this->table1, $content); + $this->assertContains($this->table2, $content); + } + + public function doTestSchemaRemoving() { + OC_DB::removeDBStructure($this->schema_file); + $this->assertTableNotExist($this->table1); + $this->assertTableNotExist($this->table2); + } + + /** + * @param string $table + */ + public function assertTableExist($table) { + $this->assertTrue(OC_DB::tableExists($table), 'Table ' . $table . ' does not exist'); + } + + /** + * @param string $table + */ + public function assertTableNotExist($table) { + $platform = \OC::$server->getDatabaseConnection()->getDatabasePlatform(); + if ($platform instanceof \Doctrine\DBAL\Platforms\SqlitePlatform) { + // sqlite removes the tables after closing the DB + $this->assertTrue(true); + } else { + $this->assertFalse(OC_DB::tableExists($table), 'Table ' . $table . ' exists.'); + } + } +} diff --git a/tests/lib/DB/LegacyDBTest.php b/tests/lib/DB/LegacyDBTest.php new file mode 100644 index 00000000000..7aeeb3dd1f9 --- /dev/null +++ b/tests/lib/DB/LegacyDBTest.php @@ -0,0 +1,393 @@ +<?php +/** + * Copyright (c) 2012 Bart Visscher <bartv@thisnet.nl> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + +namespace Test\DB; + +use OC_DB; + +/** + * Class LegacyDBTest + * + * @group DB + */ +class LegacyDBTest extends \Test\TestCase { + protected $backupGlobals = FALSE; + + protected static $schema_file = 'static://test_db_scheme'; + protected $test_prefix; + + /** + * @var string + */ + private $table1; + + /** + * @var string + */ + private $table2; + + /** + * @var string + */ + private $table3; + + /** + * @var string + */ + private $table4; + + /** + * @var string + */ + private $table5; + + protected function setUp() { + parent::setUp(); + + $dbFile = \OC::$SERVERROOT.'/tests/data/db_structure.xml'; + + $r = $this->getUniqueID('_', 4).'_'; + $content = file_get_contents( $dbFile ); + $content = str_replace( '*dbprefix*', '*dbprefix*'.$r, $content ); + file_put_contents( self::$schema_file, $content ); + OC_DB::createDbFromStructure(self::$schema_file); + + $this->test_prefix = $r; + $this->table1 = $this->test_prefix.'cntcts_addrsbks'; + $this->table2 = $this->test_prefix.'cntcts_cards'; + $this->table3 = $this->test_prefix.'vcategory'; + $this->table4 = $this->test_prefix.'decimal'; + $this->table5 = $this->test_prefix.'uniconst'; + } + + protected function tearDown() { + OC_DB::removeDBStructure(self::$schema_file); + unlink(self::$schema_file); + + parent::tearDown(); + } + + public function testQuotes() { + $query = OC_DB::prepare('SELECT `fullname` FROM `*PREFIX*'.$this->table2.'` WHERE `uri` = ?'); + $result = $query->execute(array('uri_1')); + $this->assertTrue((bool)$result); + $row = $result->fetchRow(); + $this->assertFalse($row); + $query = OC_DB::prepare('INSERT INTO `*PREFIX*'.$this->table2.'` (`fullname`,`uri`) VALUES (?,?)'); + $result = $query->execute(array('fullname test', 'uri_1')); + $this->assertEquals(1, $result); + $query = OC_DB::prepare('SELECT `fullname`,`uri` FROM `*PREFIX*'.$this->table2.'` WHERE `uri` = ?'); + $result = $query->execute(array('uri_1')); + $this->assertTrue((bool)$result); + $row = $result->fetchRow(); + $this->assertArrayHasKey('fullname', $row); + $this->assertEquals($row['fullname'], 'fullname test'); + $row = $result->fetchRow(); + $this->assertFalse((bool)$row); //PDO returns false, MDB2 returns null + } + + /** + * @medium + */ + public function testNOW() { + $query = OC_DB::prepare('INSERT INTO `*PREFIX*'.$this->table2.'` (`fullname`,`uri`) VALUES (NOW(),?)'); + $result = $query->execute(array('uri_2')); + $this->assertEquals(1, $result); + $query = OC_DB::prepare('SELECT `fullname`,`uri` FROM `*PREFIX*'.$this->table2.'` WHERE `uri` = ?'); + $result = $query->execute(array('uri_2')); + $this->assertTrue((bool)$result); + } + + public function testUNIX_TIMESTAMP() { + $query = OC_DB::prepare('INSERT INTO `*PREFIX*'.$this->table2.'` (`fullname`,`uri`) VALUES (UNIX_TIMESTAMP(),?)'); + $result = $query->execute(array('uri_3')); + $this->assertEquals(1, $result); + $query = OC_DB::prepare('SELECT `fullname`,`uri` FROM `*PREFIX*'.$this->table2.'` WHERE `uri` = ?'); + $result = $query->execute(array('uri_3')); + $this->assertTrue((bool)$result); + } + + public function testLastInsertId() { + $query = OC_DB::prepare('INSERT INTO `*PREFIX*'.$this->table2.'` (`fullname`,`uri`) VALUES (?,?)'); + $result1 = OC_DB::executeAudited($query, array('insertid 1','uri_1')); + $id1 = \OC::$server->getDatabaseConnection()->lastInsertId('*PREFIX*'.$this->table2); + + // we don't know the id we should expect, so insert another row + $result2 = OC_DB::executeAudited($query, array('insertid 2','uri_2')); + $id2 = \OC::$server->getDatabaseConnection()->lastInsertId('*PREFIX*'.$this->table2); + // now we can check if the two ids are in correct order + $this->assertGreaterThan($id1, $id2); + } + + public function testinsertIfNotExist() { + $categoryEntries = array( + array('user' => 'test', 'type' => 'contact', 'category' => 'Family', 'expectedResult' => 1), + array('user' => 'test', 'type' => 'contact', 'category' => 'Friends', 'expectedResult' => 1), + array('user' => 'test', 'type' => 'contact', 'category' => 'Coworkers', 'expectedResult' => 1), + array('user' => 'test', 'type' => 'contact', 'category' => 'Coworkers', 'expectedResult' => 0), + array('user' => 'test', 'type' => 'contact', 'category' => 'School', 'expectedResult' => 1), + ); + + foreach($categoryEntries as $entry) { + $result = \OCP\DB::insertIfNotExist('*PREFIX*'.$this->table3, + array( + 'uid' => $entry['user'], + 'type' => $entry['type'], + 'category' => $entry['category'], + )); + $this->assertEquals($entry['expectedResult'], $result); + } + + $query = OC_DB::prepare('SELECT * FROM `*PREFIX*'.$this->table3.'`'); + $result = $query->execute(); + $this->assertTrue((bool)$result); + $this->assertEquals(4, count($result->fetchAll())); + } + + public function testInsertIfNotExistNull() { + $categoryEntries = array( + array('addressbookid' => 123, 'fullname' => null, 'expectedResult' => 1), + array('addressbookid' => 123, 'fullname' => null, 'expectedResult' => 0), + array('addressbookid' => 123, 'fullname' => 'test', 'expectedResult' => 1), + ); + + foreach($categoryEntries as $entry) { + $result = \OCP\DB::insertIfNotExist('*PREFIX*'.$this->table2, + array( + 'addressbookid' => $entry['addressbookid'], + 'fullname' => $entry['fullname'], + )); + $this->assertEquals($entry['expectedResult'], $result); + } + + $query = OC_DB::prepare('SELECT * FROM `*PREFIX*'.$this->table2.'`'); + $result = $query->execute(); + $this->assertTrue((bool)$result); + $this->assertEquals(2, count($result->fetchAll())); + } + + public function testInsertIfNotExistDonTOverwrite() { + $fullName = 'fullname test'; + $uri = 'uri_1'; + $carddata = 'This is a vCard'; + + // Normal test to have same known data inserted. + $query = OC_DB::prepare('INSERT INTO `*PREFIX*'.$this->table2.'` (`fullname`, `uri`, `carddata`) VALUES (?, ?, ?)'); + $result = $query->execute(array($fullName, $uri, $carddata)); + $this->assertEquals(1, $result); + $query = OC_DB::prepare('SELECT `fullname`, `uri`, `carddata` FROM `*PREFIX*'.$this->table2.'` WHERE `uri` = ?'); + $result = $query->execute(array($uri)); + $this->assertTrue((bool)$result); + $rowset = $result->fetchAll(); + $this->assertEquals(1, count($rowset)); + $this->assertArrayHasKey('carddata', $rowset[0]); + $this->assertEquals($carddata, $rowset[0]['carddata']); + + // Try to insert a new row + $result = \OCP\DB::insertIfNotExist('*PREFIX*'.$this->table2, + array( + 'fullname' => $fullName, + 'uri' => $uri, + )); + $this->assertEquals(0, $result); + + $query = OC_DB::prepare('SELECT `fullname`, `uri`, `carddata` FROM `*PREFIX*'.$this->table2.'` WHERE `uri` = ?'); + $result = $query->execute(array($uri)); + $this->assertTrue((bool)$result); + // Test that previously inserted data isn't overwritten + // And that a new row hasn't been inserted. + $rowset = $result->fetchAll(); + $this->assertEquals(1, count($rowset)); + $this->assertArrayHasKey('carddata', $rowset[0]); + $this->assertEquals($carddata, $rowset[0]['carddata']); + } + + public function testInsertIfNotExistsViolating() { + $result = \OCP\DB::insertIfNotExist('*PREFIX*'.$this->table5, + array( + 'storage' => 1, + 'path_hash' => md5('welcome.txt'), + 'etag' => $this->getUniqueID() + )); + $this->assertEquals(1, $result); + + $result = \OCP\DB::insertIfNotExist('*PREFIX*'.$this->table5, + array( + 'storage' => 1, + 'path_hash' => md5('welcome.txt'), + 'etag' => $this->getUniqueID() + ),['storage', 'path_hash']); + + $this->assertEquals(0, $result); + } + + public function insertIfNotExistsViolatingThrows() { + return [ + [null], + [['etag']], + ]; + } + + /** + * @dataProvider insertIfNotExistsViolatingThrows + * @expectedException \Doctrine\DBAL\Exception\UniqueConstraintViolationException + * + * @param array $compareKeys + */ + public function testInsertIfNotExistsViolatingThrows($compareKeys) { + $result = \OCP\DB::insertIfNotExist('*PREFIX*'.$this->table5, + array( + 'storage' => 1, + 'path_hash' => md5('welcome.txt'), + 'etag' => $this->getUniqueID() + )); + $this->assertEquals(1, $result); + + $result = \OCP\DB::insertIfNotExist('*PREFIX*'.$this->table5, + array( + 'storage' => 1, + 'path_hash' => md5('welcome.txt'), + 'etag' => $this->getUniqueID() + ), $compareKeys); + + $this->assertEquals(0, $result); + } + + public function testUtf8Data() { + $table = "*PREFIX*{$this->table2}"; + $expected = "Ћö雙喜\xE2\x80\xA2"; + + $query = OC_DB::prepare("INSERT INTO `$table` (`fullname`, `uri`, `carddata`) VALUES (?, ?, ?)"); + $result = $query->execute(array($expected, 'uri_1', 'This is a vCard')); + $this->assertEquals(1, $result); + + $actual = OC_DB::prepare("SELECT `fullname` FROM `$table`")->execute()->fetchOne(); + $this->assertSame($expected, $actual); + } + + /** + * Insert, select and delete decimal(12,2) values + * @dataProvider decimalData + */ + public function testDecimal($insert, $expect) { + $table = "*PREFIX*" . $this->table4; + $rowname = 'decimaltest'; + + $query = OC_DB::prepare('INSERT INTO `' . $table . '` (`' . $rowname . '`) VALUES (?)'); + $result = $query->execute(array($insert)); + $this->assertEquals(1, $result); + $query = OC_DB::prepare('SELECT `' . $rowname . '` FROM `' . $table . '`'); + $result = $query->execute(); + $this->assertTrue((bool)$result); + $row = $result->fetchRow(); + $this->assertArrayHasKey($rowname, $row); + $this->assertEquals($expect, $row[$rowname]); + $query = OC_DB::prepare('DELETE FROM `' . $table . '`'); + $result = $query->execute(); + $this->assertTrue((bool)$result); + } + + public function decimalData() { + return [ + ['1337133713.37', '1337133713.37'], + ['1234567890', '1234567890.00'], + ]; + } + + public function testUpdateAffectedRowsNoMatch() { + $this->insertCardData('fullname1', 'uri1'); + // The WHERE clause does not match any rows + $this->assertSame(0, $this->updateCardData('fullname3', 'uri2')); + } + + public function testUpdateAffectedRowsDifferent() { + $this->insertCardData('fullname1', 'uri1'); + // The WHERE clause matches a single row and the value we are updating + // is different from the one already present. + $this->assertSame(1, $this->updateCardData('fullname1', 'uri2')); + } + + public function testUpdateAffectedRowsSame() { + $this->insertCardData('fullname1', 'uri1'); + // The WHERE clause matches a single row and the value we are updating + // to is the same as the one already present. MySQL reports 0 here when + // the PDO::MYSQL_ATTR_FOUND_ROWS flag is not specified. + $this->assertSame(1, $this->updateCardData('fullname1', 'uri1')); + } + + public function testUpdateAffectedRowsMultiple() { + $this->insertCardData('fullname1', 'uri1'); + $this->insertCardData('fullname2', 'uri2'); + // The WHERE clause matches two rows. One row contains a value that + // needs to be updated, the other one already contains the value we are + // updating to. MySQL reports 1 here when the PDO::MYSQL_ATTR_FOUND_ROWS + // flag is not specified. + $query = OC_DB::prepare("UPDATE `*PREFIX*{$this->table2}` SET `uri` = ?"); + $this->assertSame(2, $query->execute(array('uri1'))); + } + + protected function insertCardData($fullname, $uri) { + $query = OC_DB::prepare("INSERT INTO `*PREFIX*{$this->table2}` (`fullname`, `uri`, `carddata`) VALUES (?, ?, ?)"); + $this->assertSame(1, $query->execute(array($fullname, $uri, $this->getUniqueID()))); + } + + protected function updateCardData($fullname, $uri) { + $query = OC_DB::prepare("UPDATE `*PREFIX*{$this->table2}` SET `uri` = ? WHERE `fullname` = ?"); + return $query->execute(array($uri, $fullname)); + } + + public function testILIKE() { + $table = "*PREFIX*{$this->table2}"; + + $query = OC_DB::prepare("INSERT INTO `$table` (`fullname`, `uri`, `carddata`) VALUES (?, ?, ?)"); + $query->execute(array('fooBAR', 'foo', 'bar')); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?"); + $result = $query->execute(array('foobar')); + $this->assertCount(0, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); + $result = $query->execute(array('foobar')); + $this->assertCount(1, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); + $result = $query->execute(array('foo')); + $this->assertCount(0, $result->fetchAll()); + } + + public function testILIKEWildcard() { + $table = "*PREFIX*{$this->table2}"; + + $query = OC_DB::prepare("INSERT INTO `$table` (`fullname`, `uri`, `carddata`) VALUES (?, ?, ?)"); + $query->execute(array('FooBAR', 'foo', 'bar')); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?"); + $result = $query->execute(array('%bar')); + $this->assertCount(0, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?"); + $result = $query->execute(array('foo%')); + $this->assertCount(0, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?"); + $result = $query->execute(array('%ba%')); + $this->assertCount(0, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); + $result = $query->execute(array('%bar')); + $this->assertCount(1, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); + $result = $query->execute(array('foo%')); + $this->assertCount(1, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); + $result = $query->execute(array('%ba%')); + $this->assertCount(1, $result->fetchAll()); + } +} diff --git a/tests/lib/DB/MDB2SchemaManagerTest.php b/tests/lib/DB/MDB2SchemaManagerTest.php new file mode 100644 index 00000000000..93078b4f722 --- /dev/null +++ b/tests/lib/DB/MDB2SchemaManagerTest.php @@ -0,0 +1,52 @@ +<?php + +/** + * Copyright (c) 2014 Thomas Müller <deepdiver@owncloud.com> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + +namespace Test\DB; + +use Doctrine\DBAL\Platforms\OraclePlatform; + +/** + * Class MDB2SchemaManager + * + * @group DB + * + * @package Test\DB + */ +class MDB2SchemaManagerTest extends \Test\TestCase { + + protected function tearDown() { + // do not drop the table for Oracle as it will create a bogus transaction + // that will break the following test suites requiring transactions + if (\OC::$server->getConfig()->getSystemValue('dbtype', 'sqlite') !== 'oci') { + \OC::$server->getDatabaseConnection()->dropTable('table'); + } + + parent::tearDown(); + } + + public function testAutoIncrement() { + + $connection = \OC::$server->getDatabaseConnection(); + if ($connection->getDatabasePlatform() instanceof OraclePlatform) { + $this->markTestSkipped('Adding auto increment columns in Oracle is not supported.'); + } + + $manager = new \OC\DB\MDB2SchemaManager($connection); + + $manager->createDbFromStructure(__DIR__ . '/ts-autoincrement-before.xml'); + $connection->executeUpdate('insert into `*PREFIX*table` values (?)', array('abc')); + $connection->executeUpdate('insert into `*PREFIX*table` values (?)', array('abc')); + $connection->executeUpdate('insert into `*PREFIX*table` values (?)', array('123')); + $connection->executeUpdate('insert into `*PREFIX*table` values (?)', array('123')); + $manager->updateDbFromStructure(__DIR__ . '/ts-autoincrement-after.xml'); + + $this->assertTrue(true); + } + +} diff --git a/tests/lib/DB/MDB2SchemaReaderTest.php b/tests/lib/DB/MDB2SchemaReaderTest.php new file mode 100644 index 00000000000..22c0ba28569 --- /dev/null +++ b/tests/lib/DB/MDB2SchemaReaderTest.php @@ -0,0 +1,83 @@ +<?php + +/** + * Copyright (c) 2013 Robin Appelman <icewind@owncloud.com> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + +namespace Test\DB; + +use Doctrine\DBAL\Platforms\MySqlPlatform; + +class MDB2SchemaReaderTest extends \Test\TestCase { + /** + * @var \OC\DB\MDB2SchemaReader $reader + */ + protected $reader; + + /** + * @return \OC\Config + */ + protected function getConfig() { + $config = $this->getMockBuilder('\OCP\IConfig') + ->disableOriginalConstructor() + ->getMock(); + $config->expects($this->any()) + ->method('getSystemValue') + ->will($this->returnValueMap(array( + array('dbname', 'owncloud', 'testDB'), + array('dbtableprefix', 'oc_', 'test_') + ))); + return $config; + } + + public function testRead() { + $reader = new \OC\DB\MDB2SchemaReader($this->getConfig(), new MySqlPlatform()); + $schema = $reader->loadSchemaFromFile(__DIR__ . '/testschema.xml'); + $this->assertCount(1, $schema->getTables()); + + $table = $schema->getTable('test_table'); + $this->assertCount(8, $table->getColumns()); + + $this->assertEquals(4, $table->getColumn('integerfield')->getLength()); + $this->assertTrue($table->getColumn('integerfield')->getAutoincrement()); + $this->assertNull($table->getColumn('integerfield')->getDefault()); + $this->assertTrue($table->getColumn('integerfield')->getNotnull()); + $this->assertInstanceOf('Doctrine\DBAL\Types\IntegerType', $table->getColumn('integerfield')->getType()); + + $this->assertSame(10, $table->getColumn('integerfield_default')->getDefault()); + + $this->assertEquals(32, $table->getColumn('textfield')->getLength()); + $this->assertFalse($table->getColumn('textfield')->getAutoincrement()); + $this->assertSame('foo', $table->getColumn('textfield')->getDefault()); + $this->assertTrue($table->getColumn('textfield')->getNotnull()); + $this->assertInstanceOf('Doctrine\DBAL\Types\StringType', $table->getColumn('textfield')->getType()); + + $this->assertNull($table->getColumn('clobfield')->getLength()); + $this->assertFalse($table->getColumn('clobfield')->getAutoincrement()); + $this->assertNull($table->getColumn('clobfield')->getDefault()); + $this->assertTrue($table->getColumn('clobfield')->getNotnull()); + $this->assertInstanceOf('Doctrine\DBAL\Types\TextType', $table->getColumn('clobfield')->getType()); + + $this->assertNull($table->getColumn('booleanfield')->getLength()); + $this->assertFalse($table->getColumn('booleanfield')->getAutoincrement()); + $this->assertNull($table->getColumn('booleanfield')->getDefault()); + $this->assertInstanceOf('Doctrine\DBAL\Types\BooleanType', $table->getColumn('booleanfield')->getType()); + + $this->assertTrue($table->getColumn('booleanfield_true')->getDefault()); + $this->assertFalse($table->getColumn('booleanfield_false')->getDefault()); + + $this->assertEquals(12, $table->getColumn('decimalfield_precision_scale')->getPrecision()); + $this->assertEquals(2, $table->getColumn('decimalfield_precision_scale')->getScale()); + + $this->assertCount(2, $table->getIndexes()); + $this->assertEquals(array('integerfield'), $table->getIndex('primary')->getUnquotedColumns()); + $this->assertTrue($table->getIndex('primary')->isPrimary()); + $this->assertTrue($table->getIndex('primary')->isUnique()); + $this->assertEquals(array('booleanfield'), $table->getIndex('index_boolean')->getUnquotedColumns()); + $this->assertFalse($table->getIndex('index_boolean')->isPrimary()); + $this->assertFalse($table->getIndex('index_boolean')->isUnique()); + } +} diff --git a/tests/lib/DB/MigratorTest.php b/tests/lib/DB/MigratorTest.php new file mode 100644 index 00000000000..e4f45c4bb86 --- /dev/null +++ b/tests/lib/DB/MigratorTest.php @@ -0,0 +1,203 @@ +<?php + +/** + * Copyright (c) 2014 Robin Appelman <icewind@owncloud.com> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + +namespace Test\DB; + +use \Doctrine\DBAL\DBALException; +use Doctrine\DBAL\Platforms\OraclePlatform; +use \Doctrine\DBAL\Schema\Schema; +use \Doctrine\DBAL\Schema\SchemaConfig; +use OCP\IConfig; + +/** + * Class MigratorTest + * + * @group DB + * + * @package Test\DB + */ +class MigratorTest extends \Test\TestCase { + /** + * @var \Doctrine\DBAL\Connection $connection + */ + private $connection; + + /** + * @var \OC\DB\MDB2SchemaManager + */ + private $manager; + + /** + * @var IConfig + **/ + private $config; + + /** @var string */ + private $tableName; + + protected function setUp() { + parent::setUp(); + + $this->config = \OC::$server->getConfig(); + $this->connection = \OC::$server->getDatabaseConnection(); + if ($this->connection->getDatabasePlatform() instanceof OraclePlatform) { + $this->markTestSkipped('DB migration tests are not supported on OCI'); + } + $this->manager = new \OC\DB\MDB2SchemaManager($this->connection); + $this->tableName = strtolower($this->getUniqueID($this->config->getSystemValue('dbtableprefix', 'oc_') . 'test_')); + } + + protected function tearDown() { + $this->connection->exec('DROP TABLE ' . $this->tableName); + parent::tearDown(); + } + + /** + * @return \Doctrine\DBAL\Schema\Schema[] + */ + private function getDuplicateKeySchemas() { + $startSchema = new Schema(array(), array(), $this->getSchemaConfig()); + $table = $startSchema->createTable($this->tableName); + $table->addColumn('id', 'integer'); + $table->addColumn('name', 'string'); + $table->addIndex(array('id'), $this->tableName . '_id'); + + $endSchema = new Schema(array(), array(), $this->getSchemaConfig()); + $table = $endSchema->createTable($this->tableName); + $table->addColumn('id', 'integer'); + $table->addColumn('name', 'string'); + $table->addUniqueIndex(array('id'), $this->tableName . '_id'); + + return array($startSchema, $endSchema); + } + + private function getSchemaConfig() { + $config = new SchemaConfig(); + $config->setName($this->connection->getDatabase()); + return $config; + } + + private function isSQLite() { + return $this->connection->getDriver() instanceof \Doctrine\DBAL\Driver\PDOSqlite\Driver; + } + + /** + * @expectedException \OC\DB\MigrationException + */ + public function testDuplicateKeyUpgrade() { + if ($this->isSQLite()) { + $this->markTestSkipped('sqlite does not throw errors when creating a new key on existing data'); + } + list($startSchema, $endSchema) = $this->getDuplicateKeySchemas(); + $migrator = $this->manager->getMigrator(); + $migrator->migrate($startSchema); + + $this->connection->insert($this->tableName, array('id' => 1, 'name' => 'foo')); + $this->connection->insert($this->tableName, array('id' => 2, 'name' => 'bar')); + $this->connection->insert($this->tableName, array('id' => 2, 'name' => 'qwerty')); + + $migrator->checkMigrate($endSchema); + $this->fail('checkMigrate should have failed'); + } + + public function testUpgrade() { + list($startSchema, $endSchema) = $this->getDuplicateKeySchemas(); + $migrator = $this->manager->getMigrator(); + $migrator->migrate($startSchema); + + $this->connection->insert($this->tableName, array('id' => 1, 'name' => 'foo')); + $this->connection->insert($this->tableName, array('id' => 2, 'name' => 'bar')); + $this->connection->insert($this->tableName, array('id' => 3, 'name' => 'qwerty')); + + $migrator->checkMigrate($endSchema); + $migrator->migrate($endSchema); + $this->assertTrue(true); + } + + public function testUpgradeDifferentPrefix() { + $oldTablePrefix = $this->config->getSystemValue('dbtableprefix', 'oc_'); + + $this->config->setSystemValue('dbtableprefix', 'ownc_'); + $this->tableName = strtolower($this->getUniqueID($this->config->getSystemValue('dbtableprefix') . 'test_')); + + list($startSchema, $endSchema) = $this->getDuplicateKeySchemas(); + $migrator = $this->manager->getMigrator(); + $migrator->migrate($startSchema); + + $this->connection->insert($this->tableName, array('id' => 1, 'name' => 'foo')); + $this->connection->insert($this->tableName, array('id' => 2, 'name' => 'bar')); + $this->connection->insert($this->tableName, array('id' => 3, 'name' => 'qwerty')); + + $migrator->checkMigrate($endSchema); + $migrator->migrate($endSchema); + $this->assertTrue(true); + + $this->config->setSystemValue('dbtableprefix', $oldTablePrefix); + } + + public function testInsertAfterUpgrade() { + list($startSchema, $endSchema) = $this->getDuplicateKeySchemas(); + $migrator = $this->manager->getMigrator(); + $migrator->migrate($startSchema); + + $migrator->migrate($endSchema); + + $this->connection->insert($this->tableName, array('id' => 1, 'name' => 'foo')); + $this->connection->insert($this->tableName, array('id' => 2, 'name' => 'bar')); + try { + $this->connection->insert($this->tableName, array('id' => 2, 'name' => 'qwerty')); + $this->fail('Expected duplicate key insert to fail'); + } catch (DBALException $e) { + $this->assertTrue(true); + } + } + + public function testAddingPrimaryKeyWithAutoIncrement() { + $startSchema = new Schema(array(), array(), $this->getSchemaConfig()); + $table = $startSchema->createTable($this->tableName); + $table->addColumn('id', 'integer'); + $table->addColumn('name', 'string'); + + $endSchema = new Schema(array(), array(), $this->getSchemaConfig()); + $table = $endSchema->createTable($this->tableName); + $table->addColumn('id', 'integer', array('autoincrement' => true)); + $table->addColumn('name', 'string'); + $table->setPrimaryKey(array('id')); + + $migrator = $this->manager->getMigrator(); + $migrator->migrate($startSchema); + + $migrator->checkMigrate($endSchema); + $migrator->migrate($endSchema); + + $this->assertTrue(true); + } + + public function testReservedKeywords() { + $startSchema = new Schema(array(), array(), $this->getSchemaConfig()); + $table = $startSchema->createTable($this->tableName); + $table->addColumn('id', 'integer', array('autoincrement' => true)); + $table->addColumn('user', 'string', array('length' => 255)); + $table->setPrimaryKey(array('id')); + + $endSchema = new Schema(array(), array(), $this->getSchemaConfig()); + $table = $endSchema->createTable($this->tableName); + $table->addColumn('id', 'integer', array('autoincrement' => true)); + $table->addColumn('user', 'string', array('length' => 64)); + $table->setPrimaryKey(array('id')); + + $migrator = $this->manager->getMigrator(); + $migrator->migrate($startSchema); + + $migrator->checkMigrate($endSchema); + $migrator->migrate($endSchema); + + $this->assertTrue(true); + } +} diff --git a/tests/lib/DB/MySqlMigrationTest.php b/tests/lib/DB/MySqlMigrationTest.php new file mode 100644 index 00000000000..cdc7ef47784 --- /dev/null +++ b/tests/lib/DB/MySqlMigrationTest.php @@ -0,0 +1,49 @@ +<?php +/** + * Copyright (c) 2014 Thomas Müller <deepdiver@owncloud.com> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + +namespace Test\DB; + +/** + * Class MySqlMigration + * + * @group DB + */ +class MySqlMigrationTest extends \Test\TestCase { + + /** @var \Doctrine\DBAL\Connection */ + private $connection; + + /** @var string */ + private $tableName; + + protected function setUp() { + parent::setUp(); + + $this->connection = \OC::$server->getDatabaseConnection(); + if (!$this->connection->getDatabasePlatform() instanceof \Doctrine\DBAL\Platforms\MySqlPlatform) { + $this->markTestSkipped("Test only relevant on MySql"); + } + + $dbPrefix = \OC::$server->getConfig()->getSystemValue("dbtableprefix"); + $this->tableName = $this->getUniqueID($dbPrefix . '_enum_bit_test'); + $this->connection->exec("CREATE TABLE $this->tableName(b BIT, e ENUM('1','2','3','4'))"); + } + + protected function tearDown() { + $this->connection->getSchemaManager()->dropTable($this->tableName); + parent::tearDown(); + } + + public function testNonOCTables() { + $manager = new \OC\DB\MDB2SchemaManager($this->connection); + $manager->updateDbFromStructure(__DIR__ . '/testschema.xml'); + + $this->assertTrue(true); + } + +} diff --git a/tests/lib/DB/QueryBuilder/ExpressionBuilderTest.php b/tests/lib/DB/QueryBuilder/ExpressionBuilderTest.php new file mode 100644 index 00000000000..4122f300c86 --- /dev/null +++ b/tests/lib/DB/QueryBuilder/ExpressionBuilderTest.php @@ -0,0 +1,427 @@ +<?php +/** + * @author Joas Schilling <nickvergessen@owncloud.com> + * + * @copyright Copyright (c) 2015, 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 <http://www.gnu.org/licenses/> + * + */ + +namespace Test\DB\QueryBuilder; + +use Doctrine\DBAL\Query\Expression\ExpressionBuilder as DoctrineExpressionBuilder; +use OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder; +use OCP\DB\QueryBuilder\IQueryBuilder; +use Test\TestCase; + +/** + * Class ExpressionBuilderTest + * + * @group DB + * + * @package Test\DB\QueryBuilder + */ +class ExpressionBuilderTest extends TestCase { + /** @var ExpressionBuilder */ + protected $expressionBuilder; + + /** @var DoctrineExpressionBuilder */ + protected $doctrineExpressionBuilder; + + /** @var \Doctrine\DBAL\Connection|\OCP\IDBConnection */ + protected $connection; + + protected function setUp() { + parent::setUp(); + + $this->connection = \OC::$server->getDatabaseConnection(); + + $this->expressionBuilder = new ExpressionBuilder($this->connection); + + $this->doctrineExpressionBuilder = new DoctrineExpressionBuilder($this->connection); + } + + public function dataComparison() { + $valueSets = $this->dataComparisons(); + $comparisonOperators = ['=', '<>', '<', '>', '<=', '>=']; + + $testSets = []; + foreach ($comparisonOperators as $operator) { + foreach ($valueSets as $values) { + $testSets[] = array_merge([$operator], $values); + } + } + return $testSets; + } + + /** + * @dataProvider dataComparison + * + * @param string $comparison + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testComparison($comparison, $input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->comparison($doctrineInput1, $comparison, $doctrineInput2), + $this->expressionBuilder->comparison($ocInput1, $comparison, $ocInput2) + ); + } + + public function dataComparisons() { + return [ + ['value', false, 'value', false], + ['value', false, 'value', true], + ['value', true, 'value', false], + ['value', true, 'value', true], + ]; + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testEquals($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->eq($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->eq($ocInput1, $ocInput2) + ); + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testNotEquals($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->neq($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->neq($ocInput1, $ocInput2) + ); + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testLowerThan($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->lt($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->lt($ocInput1, $ocInput2) + ); + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testLowerThanEquals($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->lte($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->lte($ocInput1, $ocInput2) + ); + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testGreaterThan($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->gt($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->gt($ocInput1, $ocInput2) + ); + } + + /** + * @dataProvider dataComparisons + * + * @param mixed $input1 + * @param bool $isInput1Literal + * @param mixed $input2 + * @param bool $isInput2Literal + */ + public function testGreaterThanEquals($input1, $isInput1Literal, $input2, $isInput2Literal) { + list($doctrineInput1, $ocInput1) = $this->helpWithLiteral($input1, $isInput1Literal); + list($doctrineInput2, $ocInput2) = $this->helpWithLiteral($input2, $isInput2Literal); + + $this->assertEquals( + $this->doctrineExpressionBuilder->gte($doctrineInput1, $doctrineInput2), + $this->expressionBuilder->gte($ocInput1, $ocInput2) + ); + } + + public function testIsNull() { + $this->assertEquals( + $this->doctrineExpressionBuilder->isNull('`test`'), + $this->expressionBuilder->isNull('test') + ); + } + + public function testIsNotNull() { + $this->assertEquals( + $this->doctrineExpressionBuilder->isNotNull('`test`'), + $this->expressionBuilder->isNotNull('test') + ); + } + + public function dataLike() { + return [ + ['value', false], + ['value', true], + ]; + } + + /** + * @dataProvider dataLike + * + * @param mixed $input + * @param bool $isLiteral + */ + public function testLike($input, $isLiteral) { + list($doctrineInput, $ocInput) = $this->helpWithLiteral($input, $isLiteral); + + $this->assertEquals( + $this->doctrineExpressionBuilder->like('`test`', $doctrineInput), + $this->expressionBuilder->like('test', $ocInput) + ); + } + + /** + * @dataProvider dataLike + * + * @param mixed $input + * @param bool $isLiteral + */ + public function testNotLike($input, $isLiteral) { + list($doctrineInput, $ocInput) = $this->helpWithLiteral($input, $isLiteral); + + $this->assertEquals( + $this->doctrineExpressionBuilder->notLike('`test`', $doctrineInput), + $this->expressionBuilder->notLike('test', $ocInput) + ); + } + + public function dataIn() { + return [ + ['value', false], + ['value', true], + [['value'], false], + [['value'], true], + ]; + } + + /** + * @dataProvider dataIn + * + * @param mixed $input + * @param bool $isLiteral + */ + public function testIn($input, $isLiteral) { + list($doctrineInput, $ocInput) = $this->helpWithLiteral($input, $isLiteral); + + $this->assertEquals( + $this->doctrineExpressionBuilder->in('`test`', $doctrineInput), + $this->expressionBuilder->in('test', $ocInput) + ); + } + + /** + * @dataProvider dataIn + * + * @param mixed $input + * @param bool $isLiteral + */ + public function testNotIn($input, $isLiteral) { + list($doctrineInput, $ocInput) = $this->helpWithLiteral($input, $isLiteral); + + $this->assertEquals( + $this->doctrineExpressionBuilder->notIn('`test`', $doctrineInput), + $this->expressionBuilder->notIn('test', $ocInput) + ); + } + + protected function helpWithLiteral($input, $isLiteral) { + if ($isLiteral) { + if (is_array($input)) { + $doctrineInput = array_map(function ($ident) { + return $this->doctrineExpressionBuilder->literal($ident); + }, $input); + $ocInput = array_map(function ($ident) { + return $this->expressionBuilder->literal($ident); + }, $input); + } else { + $doctrineInput = $this->doctrineExpressionBuilder->literal($input); + $ocInput = $this->expressionBuilder->literal($input); + } + } else { + if (is_array($input)) { + $doctrineInput = array_map(function ($input) { + return '`' . $input . '`'; + }, $input); + $ocInput = $input; + } else { + $doctrineInput = '`' . $input . '`'; + $ocInput = $input; + } + } + + return [$doctrineInput, $ocInput]; + } + + public function dataLiteral() { + return [ + ['value', null], + ['1', null], + [1, null], + [1, 'string'], + [1, 'integer'], + [1, IQueryBuilder::PARAM_INT], + ]; + } + + /** + * @dataProvider dataLiteral + * + * @param mixed $input + * @param string|null $type + */ + public function testLiteral($input, $type) { + /** @var \OC\DB\QueryBuilder\Literal $actual */ + $actual = $this->expressionBuilder->literal($input, $type); + + $this->assertInstanceOf('\OC\DB\QueryBuilder\Literal', $actual); + $this->assertEquals( + $this->doctrineExpressionBuilder->literal($input, $type), + $actual->__toString() + ); + } + + public function dataClobComparisons() { + return [ + ['eq', '5', IQueryBuilder::PARAM_STR, false, 3], + ['eq', '5', IQueryBuilder::PARAM_STR, true, 1], + ['neq', '5', IQueryBuilder::PARAM_STR, false, 6], + ['neq', '5', IQueryBuilder::PARAM_STR, true, 4], + ['lt', '5', IQueryBuilder::PARAM_STR, false, 3], + ['lt', '5', IQueryBuilder::PARAM_STR, true, 1], + ['lte', '5', IQueryBuilder::PARAM_STR, false, 6], + ['lte', '5', IQueryBuilder::PARAM_STR, true, 4], + ['gt', '5', IQueryBuilder::PARAM_STR, false, 3], + ['gt', '5', IQueryBuilder::PARAM_STR, true, 1], + ['gte', '5', IQueryBuilder::PARAM_STR, false, 6], + ['gte', '5', IQueryBuilder::PARAM_STR, true, 4], + ['like', '%5%', IQueryBuilder::PARAM_STR, false, 3], + ['like', '%5%', IQueryBuilder::PARAM_STR, true, 1], + ['notLike', '%5%', IQueryBuilder::PARAM_STR, false, 6], + ['notLike', '%5%', IQueryBuilder::PARAM_STR, true, 4], + ['in', ['5'], IQueryBuilder::PARAM_STR_ARRAY, false, 3], + ['in', ['5'], IQueryBuilder::PARAM_STR_ARRAY, true, 1], + ['notIn', ['5'], IQueryBuilder::PARAM_STR_ARRAY, false, 6], + ['notIn', ['5'], IQueryBuilder::PARAM_STR_ARRAY, true, 4], + ]; + } + + /** + * @dataProvider dataClobComparisons + * @param string $function + * @param mixed $value + * @param mixed $type + * @param bool $compareKeyToValue + * @param int $expected + */ + public function testClobComparisons($function, $value, $type, $compareKeyToValue, $expected) { + $appId = $this->getUniqueID('testing'); + $this->createConfig($appId, 1, 4); + $this->createConfig($appId, 2, 5); + $this->createConfig($appId, 3, 6); + $this->createConfig($appId, 4, 4); + $this->createConfig($appId, 5, 5); + $this->createConfig($appId, 6, 6); + $this->createConfig($appId, 7, 4); + $this->createConfig($appId, 8, 5); + $this->createConfig($appId, 9, 6); + + $query = $this->connection->getQueryBuilder(); + $query->select($query->createFunction('COUNT(*) AS `count`')) + ->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter($appId))) + ->andWhere(call_user_func([$query->expr(), $function], 'configvalue', $query->createNamedParameter($value, $type), IQueryBuilder::PARAM_STR)); + + if ($compareKeyToValue) { + $query->andWhere(call_user_func([$query->expr(), $function], 'configkey', 'configvalue', IQueryBuilder::PARAM_STR)); + } + + $result = $query->execute(); + + $this->assertEquals(['count' => $expected], $result->fetch()); + $result->closeCursor(); + + $query = $this->connection->getQueryBuilder(); + $query->delete('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter($appId))) + ->execute(); + } + + protected function createConfig($appId, $key, $value) { + $query = $this->connection->getQueryBuilder(); + $query->insert('appconfig') + ->values([ + 'appid' => $query->createNamedParameter($appId), + 'configkey' => $query->createNamedParameter((string) $key), + 'configvalue' => $query->createNamedParameter((string) $value), + ]) + ->execute(); + } +} diff --git a/tests/lib/DB/QueryBuilder/QueryBuilderTest.php b/tests/lib/DB/QueryBuilder/QueryBuilderTest.php new file mode 100644 index 00000000000..de8f84ac345 --- /dev/null +++ b/tests/lib/DB/QueryBuilder/QueryBuilderTest.php @@ -0,0 +1,1203 @@ +<?php +/** + * @author Joas Schilling <nickvergessen@owncloud.com> + * + * @copyright Copyright (c) 2015, 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 <http://www.gnu.org/licenses/> + * + */ + +namespace Test\DB\QueryBuilder; + +use Doctrine\DBAL\Query\Expression\CompositeExpression; +use OC\DB\QueryBuilder\Literal; +use OC\DB\QueryBuilder\Parameter; +use OC\DB\QueryBuilder\QueryBuilder; +use OCP\IDBConnection; + +/** + * Class QueryBuilderTest + * + * @group DB + * + * @package Test\DB\QueryBuilder + */ +class QueryBuilderTest extends \Test\TestCase { + /** @var QueryBuilder */ + protected $queryBuilder; + + /** @var IDBConnection */ + protected $connection; + + protected function setUp() { + parent::setUp(); + + $this->connection = \OC::$server->getDatabaseConnection(); + $this->queryBuilder = new QueryBuilder($this->connection); + } + + protected function createTestingRows($appId = 'testFirstResult') { + $qB = $this->connection->getQueryBuilder(); + for ($i = 1; $i < 10; $i++) { + $qB->insert('*PREFIX*appconfig') + ->values([ + 'appid' => $qB->expr()->literal($appId), + 'configkey' => $qB->expr()->literal('testing' . $i), + 'configvalue' => $qB->expr()->literal(100 - $i), + ]) + ->execute(); + } + } + + protected function getTestingRows(QueryBuilder $queryBuilder) { + $queryBuilder->select('configvalue') + ->from('*PREFIX*appconfig') + ->where($queryBuilder->expr()->eq( + 'appid', + $queryBuilder->expr()->literal('testFirstResult') + )) + ->orderBy('configkey', 'ASC'); + + $query = $queryBuilder->execute(); + $rows = []; + while ($row = $query->fetch()) { + $rows[] = $row['configvalue']; + } + $query->closeCursor(); + + return $rows; + } + + protected function deleteTestingRows($appId = 'testFirstResult') { + $qB = $this->connection->getQueryBuilder(); + + $qB->delete('*PREFIX*appconfig') + ->where($qB->expr()->eq('appid', $qB->expr()->literal($appId))) + ->execute(); + } + + public function dataFirstResult() { + return [ + [null, [99, 98, 97, 96, 95, 94, 93, 92, 91]], + [0, [99, 98, 97, 96, 95, 94, 93, 92, 91]], + [1, [98, 97, 96, 95, 94, 93, 92, 91]], + [5, [94, 93, 92, 91]], + ]; + } + + /** + * @dataProvider dataFirstResult + * + * @param int $firstResult + * @param array $expectedSet + */ + public function testFirstResult($firstResult, $expectedSet) { + $this->deleteTestingRows(); + $this->createTestingRows(); + + if ($firstResult !== null) { + $this->queryBuilder->setFirstResult($firstResult); + + // FIXME Remove this once Doctrine/DBAL is >2.5.1: + // FIXME See https://github.com/doctrine/dbal/pull/782 + $this->queryBuilder->setMaxResults(100); + } + + $this->assertSame( + $firstResult, + $this->queryBuilder->getFirstResult() + ); + + $rows = $this->getTestingRows($this->queryBuilder); + + $this->assertCount(sizeof($expectedSet), $rows); + $this->assertEquals($expectedSet, $rows); + + $this->deleteTestingRows(); + } + + public function dataMaxResults() { + return [ + [null, [99, 98, 97, 96, 95, 94, 93, 92, 91]], + // Limit 0 gives mixed results: either all entries or none is returned + //[0, []], + [1, [99]], + [5, [99, 98, 97, 96, 95]], + ]; + } + + /** + * @dataProvider dataMaxResults + * + * @param int $maxResult + * @param array $expectedSet + */ + public function testMaxResults($maxResult, $expectedSet) { + $this->deleteTestingRows(); + $this->createTestingRows(); + + if ($maxResult !== null) { + $this->queryBuilder->setMaxResults($maxResult); + } + + $this->assertSame( + $maxResult, + $this->queryBuilder->getMaxResults() + ); + + $rows = $this->getTestingRows($this->queryBuilder); + + $this->assertCount(sizeof($expectedSet), $rows); + $this->assertEquals($expectedSet, $rows); + + $this->deleteTestingRows(); + } + + public function dataSelect() { + $queryBuilder = new QueryBuilder(\OC::$server->getDatabaseConnection()); + return [ + // select('column1') + [['configvalue'], ['configvalue' => '99']], + + // select('column1', 'column2') + [['configvalue', 'configkey'], ['configvalue' => '99', 'configkey' => 'testing1']], + + // select(['column1', 'column2']) + [[['configvalue', 'configkey']], ['configvalue' => '99', 'configkey' => 'testing1']], + + // select(new Literal('column1')) + [[$queryBuilder->expr()->literal('column1')], [], 'column1'], + + // select('column1', 'column2') + [[$queryBuilder->expr()->literal('column1'), 'configkey'], ['configkey' => 'testing1'], 'column1'], + + // select(['column1', 'column2']) + [[[$queryBuilder->expr()->literal('column1'), 'configkey']], ['configkey' => 'testing1'], 'column1'], + ]; + } + + /** + * @dataProvider dataSelect + * + * @param array $selectArguments + * @param array $expected + * @param string $expectedLiteral + */ + public function testSelect($selectArguments, $expected, $expectedLiteral = '') { + $this->deleteTestingRows(); + $this->createTestingRows(); + + call_user_func_array( + [$this->queryBuilder, 'select'], + $selectArguments + ); + + $this->queryBuilder->from('*PREFIX*appconfig') + ->where($this->queryBuilder->expr()->eq( + 'appid', + $this->queryBuilder->expr()->literal('testFirstResult') + )) + ->orderBy('configkey', 'ASC') + ->setMaxResults(1); + + $query = $this->queryBuilder->execute(); + $row = $query->fetch(); + $query->closeCursor(); + + foreach ($expected as $key => $value) { + $this->assertArrayHasKey($key, $row); + $this->assertEquals($value, $row[$key]); + unset($row[$key]); + } + + if ($expectedLiteral) { + $this->assertEquals([$expectedLiteral], array_values($row)); + } else { + $this->assertEmpty($row); + } + + $this->deleteTestingRows(); + } + + public function dataSelectAlias() { + $queryBuilder = new QueryBuilder(\OC::$server->getDatabaseConnection()); + return [ + ['configvalue', 'cv', ['cv' => '99']], + [$queryBuilder->expr()->literal('column1'), 'thing', ['thing' => 'column1']], + ]; + } + + /** + * @dataProvider dataSelectAlias + * + * @param mixed $select + * @param array $alias + * @param array $expected + */ + public function testSelectAlias($select, $alias, $expected) { + $this->deleteTestingRows(); + $this->createTestingRows(); + + $this->queryBuilder->selectAlias($select, $alias); + + $this->queryBuilder->from('*PREFIX*appconfig') + ->where($this->queryBuilder->expr()->eq( + 'appid', + $this->queryBuilder->expr()->literal('testFirstResult') + )) + ->orderBy('configkey', 'ASC') + ->setMaxResults(1); + + $query = $this->queryBuilder->execute(); + $row = $query->fetch(); + $query->closeCursor(); + + $this->assertEquals( + $expected, + $row + ); + + $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 [ + // addSelect('column1') + [['configvalue'], ['appid' => 'testFirstResult', 'configvalue' => '99']], + + // addSelect('column1', 'column2') + [['configvalue', 'configkey'], ['appid' => 'testFirstResult', 'configvalue' => '99', 'configkey' => 'testing1']], + + // addSelect(['column1', 'column2']) + [[['configvalue', 'configkey']], ['appid' => 'testFirstResult', 'configvalue' => '99', 'configkey' => 'testing1']], + + // select(new Literal('column1')) + [[$queryBuilder->expr()->literal('column1')], ['appid' => 'testFirstResult'], 'column1'], + + // select('column1', 'column2') + [[$queryBuilder->expr()->literal('column1'), 'configkey'], ['appid' => 'testFirstResult', 'configkey' => 'testing1'], 'column1'], + + // select(['column1', 'column2']) + [[[$queryBuilder->expr()->literal('column1'), 'configkey']], ['appid' => 'testFirstResult', 'configkey' => 'testing1'], 'column1'], + ]; + } + + /** + * @dataProvider dataAddSelect + * + * @param array $selectArguments + * @param array $expected + * @param string $expectedLiteral + */ + public function testAddSelect($selectArguments, $expected, $expectedLiteral = '') { + $this->deleteTestingRows(); + $this->createTestingRows(); + + $this->queryBuilder->select('appid'); + + call_user_func_array( + [$this->queryBuilder, 'addSelect'], + $selectArguments + ); + + $this->queryBuilder->from('*PREFIX*appconfig') + ->where($this->queryBuilder->expr()->eq( + 'appid', + $this->queryBuilder->expr()->literal('testFirstResult') + )) + ->orderBy('configkey', 'ASC') + ->setMaxResults(1); + + $query = $this->queryBuilder->execute(); + $row = $query->fetch(); + $query->closeCursor(); + + foreach ($expected as $key => $value) { + $this->assertArrayHasKey($key, $row); + $this->assertEquals($value, $row[$key]); + unset($row[$key]); + } + + if ($expectedLiteral) { + $this->assertEquals([$expectedLiteral], array_values($row)); + } else { + $this->assertEmpty($row); + } + + $this->deleteTestingRows(); + } + + public function dataDelete() { + return [ + ['data', null, ['table' => '`*PREFIX*data`', 'alias' => null], '`*PREFIX*data`'], + ['data', 't', ['table' => '`*PREFIX*data`', 'alias' => 't'], '`*PREFIX*data` t'], + ]; + } + + /** + * @dataProvider dataDelete + * + * @param string $tableName + * @param string $tableAlias + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testDelete($tableName, $tableAlias, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->delete($tableName, $tableAlias); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('from') + ); + + $this->assertSame( + 'DELETE FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataUpdate() { + return [ + ['data', null, ['table' => '`*PREFIX*data`', 'alias' => null], '`*PREFIX*data`'], + ['data', 't', ['table' => '`*PREFIX*data`', 'alias' => 't'], '`*PREFIX*data` t'], + ]; + } + + /** + * @dataProvider dataUpdate + * + * @param string $tableName + * @param string $tableAlias + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testUpdate($tableName, $tableAlias, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->update($tableName, $tableAlias); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('from') + ); + + $this->assertSame( + 'UPDATE ' . $expectedQuery . ' SET ', + $this->queryBuilder->getSQL() + ); + } + + public function dataInsert() { + return [ + ['data', ['table' => '`*PREFIX*data`'], '`*PREFIX*data`'], + ]; + } + + /** + * @dataProvider dataInsert + * + * @param string $tableName + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testInsert($tableName, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->insert($tableName); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('from') + ); + + $this->assertSame( + 'INSERT INTO ' . $expectedQuery . ' () VALUES()', + $this->queryBuilder->getSQL() + ); + } + + public function dataFrom() { + return [ + ['data', null, null, null, [['table' => '`*PREFIX*data`', 'alias' => null]], '`*PREFIX*data`'], + ['data', 't', null, null, [['table' => '`*PREFIX*data`', 'alias' => 't']], '`*PREFIX*data` t'], + ['data1', null, 'data2', null, [ + ['table' => '`*PREFIX*data1`', 'alias' => null], + ['table' => '`*PREFIX*data2`', 'alias' => null] + ], '`*PREFIX*data1`, `*PREFIX*data2`'], + ['data', 't1', 'data', 't2', [ + ['table' => '`*PREFIX*data`', 'alias' => 't1'], + ['table' => '`*PREFIX*data`', 'alias' => 't2'] + ], '`*PREFIX*data` t1, `*PREFIX*data` t2'], + ]; + } + + /** + * @dataProvider dataFrom + * + * @param string $table1Name + * @param string $table1Alias + * @param string $table2Name + * @param string $table2Alias + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testFrom($table1Name, $table1Alias, $table2Name, $table2Alias, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->from($table1Name, $table1Alias); + if ($table2Name !== null) { + $this->queryBuilder->from($table2Name, $table2Alias); + } + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('from') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataJoin() { + return [ + [ + 'd1', 'data2', null, null, + ['d1' => [['joinType' => 'inner', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => null, 'joinCondition' => null]]], + '`*PREFIX*data1` d1 INNER JOIN `*PREFIX*data2` ON ' + ], + [ + 'd1', 'data2', 'd2', null, + ['d1' => [['joinType' => 'inner', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => 'd2', 'joinCondition' => null]]], + '`*PREFIX*data1` d1 INNER JOIN `*PREFIX*data2` d2 ON ' + ], + [ + 'd1', 'data2', 'd2', 'd1.`field1` = d2.`field2`', + ['d1' => [['joinType' => 'inner', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => 'd2', 'joinCondition' => 'd1.`field1` = d2.`field2`']]], + '`*PREFIX*data1` d1 INNER JOIN `*PREFIX*data2` d2 ON d1.`field1` = d2.`field2`' + ], + + ]; + } + + /** + * @dataProvider dataJoin + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->from('data1', 'd1'); + $this->queryBuilder->join( + $fromAlias, + $tableName, + $tableAlias, + $condition + ); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('join') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + /** + * @dataProvider dataJoin + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testInnerJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->from('data1', 'd1'); + $this->queryBuilder->innerJoin( + $fromAlias, + $tableName, + $tableAlias, + $condition + ); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('join') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataLeftJoin() { + return [ + [ + 'd1', 'data2', null, null, + ['d1' => [['joinType' => 'left', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => null, 'joinCondition' => null]]], + '`*PREFIX*data1` d1 LEFT JOIN `*PREFIX*data2` ON ' + ], + [ + 'd1', 'data2', 'd2', null, + ['d1' => [['joinType' => 'left', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => 'd2', 'joinCondition' => null]]], + '`*PREFIX*data1` d1 LEFT JOIN `*PREFIX*data2` d2 ON ' + ], + [ + 'd1', 'data2', 'd2', 'd1.`field1` = d2.`field2`', + ['d1' => [['joinType' => 'left', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => 'd2', 'joinCondition' => 'd1.`field1` = d2.`field2`']]], + '`*PREFIX*data1` d1 LEFT JOIN `*PREFIX*data2` d2 ON d1.`field1` = d2.`field2`' + ], + ]; + } + + /** + * @dataProvider dataLeftJoin + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testLeftJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->from('data1', 'd1'); + $this->queryBuilder->leftJoin( + $fromAlias, + $tableName, + $tableAlias, + $condition + ); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('join') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataRightJoin() { + return [ + [ + 'd1', 'data2', null, null, + ['d1' => [['joinType' => 'right', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => null, 'joinCondition' => null]]], + '`*PREFIX*data1` d1 RIGHT JOIN `*PREFIX*data2` ON ' + ], + [ + 'd1', 'data2', 'd2', null, + ['d1' => [['joinType' => 'right', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => 'd2', 'joinCondition' => null]]], + '`*PREFIX*data1` d1 RIGHT JOIN `*PREFIX*data2` d2 ON ' + ], + [ + 'd1', 'data2', 'd2', 'd1.`field1` = d2.`field2`', + ['d1' => [['joinType' => 'right', 'joinTable' => '`*PREFIX*data2`', 'joinAlias' => 'd2', 'joinCondition' => 'd1.`field1` = d2.`field2`']]], + '`*PREFIX*data1` d1 RIGHT JOIN `*PREFIX*data2` d2 ON d1.`field1` = d2.`field2`' + ], + ]; + } + + /** + * @dataProvider dataRightJoin + * + * @param string $fromAlias + * @param string $tableName + * @param string $tableAlias + * @param string $condition + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testRightJoin($fromAlias, $tableName, $tableAlias, $condition, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->from('data1', 'd1'); + $this->queryBuilder->rightJoin( + $fromAlias, + $tableName, + $tableAlias, + $condition + ); + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('join') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataSet() { + return [ + ['column1', new Literal('value'), null, null, ['`column1` = value'], '`column1` = value'], + ['column1', new Parameter(':param'), null, null, ['`column1` = :param'], '`column1` = :param'], + ['column1', 'column2', null, null, ['`column1` = `column2`'], '`column1` = `column2`'], + ['column1', 'column2', 'column3', new Literal('value'), ['`column1` = `column2`', '`column3` = value'], '`column1` = `column2`, `column3` = value'], + ]; + } + + /** + * @dataProvider dataSet + * + * @param string $partOne1 + * @param string $partOne2 + * @param string $partTwo1 + * @param string $partTwo2 + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testSet($partOne1, $partOne2, $partTwo1, $partTwo2, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->update('data'); + $this->queryBuilder->set($partOne1, $partOne2); + if ($partTwo1 !== null) { + $this->queryBuilder->set($partTwo1, $partTwo2); + } + + $this->assertSame( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('set') + ); + + $this->assertSame( + 'UPDATE `*PREFIX*data` SET ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataWhere() { + return [ + [['where1'], new CompositeExpression('AND', ['where1']), 'where1'], + [['where1', 'where2'], new CompositeExpression('AND', ['where1', 'where2']), '(where1) AND (where2)'], + ]; + } + + /** + * @dataProvider dataWhere + * + * @param array $whereArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testWhere($whereArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'where'], + $whereArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('where') + ); + + $this->assertSame( + 'SELECT `column` FROM WHERE ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + /** + * @dataProvider dataWhere + * + * @param array $whereArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testAndWhere($whereArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'andWhere'], + $whereArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('where') + ); + + $this->assertSame( + 'SELECT `column` FROM WHERE ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataOrWhere() { + return [ + [['where1'], new CompositeExpression('OR', ['where1']), 'where1'], + [['where1', 'where2'], new CompositeExpression('OR', ['where1', 'where2']), '(where1) OR (where2)'], + ]; + } + + /** + * @dataProvider dataOrWhere + * + * @param array $whereArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testOrWhere($whereArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'orWhere'], + $whereArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('where') + ); + + $this->assertSame( + 'SELECT `column` FROM WHERE ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataGroupBy() { + return [ + [['column1'], ['`column1`'], '`column1`'], + [['column1', 'column2'], ['`column1`', '`column2`'], '`column1`, `column2`'], + ]; + } + + /** + * @dataProvider dataGroupBy + * + * @param array $groupByArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testGroupBy($groupByArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->select('column'); + call_user_func_array( + [$this->queryBuilder, 'groupBy'], + $groupByArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('groupBy') + ); + + $this->assertSame( + 'SELECT `column` FROM GROUP BY ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataAddGroupBy() { + return [ + [['column2'], ['`column1`', '`column2`'], '`column1`, `column2`'], + [['column2', 'column3'], ['`column1`', '`column2`', '`column3`'], '`column1`, `column2`, `column3`'], + ]; + } + + /** + * @dataProvider dataAddGroupBy + * + * @param array $groupByArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testAddGroupBy($groupByArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->select('column'); + $this->queryBuilder->groupBy('column1'); + call_user_func_array( + [$this->queryBuilder, 'addGroupBy'], + $groupByArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('groupBy') + ); + + $this->assertSame( + 'SELECT `column` FROM GROUP BY ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataSetValue() { + return [ + ['column', 'value', ['`column`' => 'value'], '(`column`) VALUES(value)'], + ]; + } + + /** + * @dataProvider dataSetValue + * + * @param string $column + * @param string $value + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testSetValue($column, $value, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->insert('data'); + $this->queryBuilder->setValue($column, $value); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('values') + ); + + $this->assertSame( + 'INSERT INTO `*PREFIX*data` ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + /** + * @dataProvider dataSetValue + * + * @param string $column + * @param string $value + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testValues($column, $value, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->insert('data'); + $this->queryBuilder->values([ + $column => $value, + ]); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('values') + ); + + $this->assertSame( + 'INSERT INTO `*PREFIX*data` ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataHaving() { + return [ + [['condition1'], new CompositeExpression('AND', ['condition1']), 'HAVING condition1'], + [['condition1', 'condition2'], new CompositeExpression('AND', ['condition1', 'condition2']), 'HAVING (condition1) AND (condition2)'], + [ + [new CompositeExpression('OR', ['condition1', 'condition2'])], + new CompositeExpression('OR', ['condition1', 'condition2']), + 'HAVING (condition1) OR (condition2)' + ], + [ + [new CompositeExpression('AND', ['condition1', 'condition2'])], + new CompositeExpression('AND', ['condition1', 'condition2']), + 'HAVING (condition1) AND (condition2)' + ], + ]; + } + + /** + * @dataProvider dataHaving + * + * @param array $havingArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testHaving($havingArguments, $expectedQueryPart, $expectedQuery) { + call_user_func_array( + [$this->queryBuilder, 'having'], + $havingArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('having') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataAndHaving() { + return [ + [['condition2'], new CompositeExpression('AND', ['condition1', 'condition2']), 'HAVING (condition1) AND (condition2)'], + [['condition2', 'condition3'], new CompositeExpression('AND', ['condition1', 'condition2', 'condition3']), 'HAVING (condition1) AND (condition2) AND (condition3)'], + [ + [new CompositeExpression('OR', ['condition2', 'condition3'])], + new CompositeExpression('AND', ['condition1', new CompositeExpression('OR', ['condition2', 'condition3'])]), + 'HAVING (condition1) AND ((condition2) OR (condition3))' + ], + [ + [new CompositeExpression('AND', ['condition2', 'condition3'])], + new CompositeExpression('AND', ['condition1', new CompositeExpression('AND', ['condition2', 'condition3'])]), + 'HAVING (condition1) AND ((condition2) AND (condition3))' + ], + ]; + } + + /** + * @dataProvider dataAndHaving + * + * @param array $havingArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testAndHaving($havingArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->having('condition1'); + call_user_func_array( + [$this->queryBuilder, 'andHaving'], + $havingArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('having') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataOrHaving() { + return [ + [['condition2'], new CompositeExpression('OR', ['condition1', 'condition2']), 'HAVING (condition1) OR (condition2)'], + [['condition2', 'condition3'], new CompositeExpression('OR', ['condition1', 'condition2', 'condition3']), 'HAVING (condition1) OR (condition2) OR (condition3)'], + [ + [new CompositeExpression('OR', ['condition2', 'condition3'])], + new CompositeExpression('OR', ['condition1', new CompositeExpression('OR', ['condition2', 'condition3'])]), + 'HAVING (condition1) OR ((condition2) OR (condition3))' + ], + [ + [new CompositeExpression('AND', ['condition2', 'condition3'])], + new CompositeExpression('OR', ['condition1', new CompositeExpression('AND', ['condition2', 'condition3'])]), + 'HAVING (condition1) OR ((condition2) AND (condition3))' + ], + ]; + } + + /** + * @dataProvider dataOrHaving + * + * @param array $havingArguments + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testOrHaving($havingArguments, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->having('condition1'); + call_user_func_array( + [$this->queryBuilder, 'orHaving'], + $havingArguments + ); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('having') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataOrderBy() { + return [ + ['column', null, ['`column` ASC'], 'ORDER BY `column` ASC'], + ['column', 'ASC', ['`column` ASC'], 'ORDER BY `column` ASC'], + ['column', 'DESC', ['`column` DESC'], 'ORDER BY `column` DESC'], + ]; + } + + /** + * @dataProvider dataOrderBy + * + * @param string $sort + * @param string $order + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testOrderBy($sort, $order, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->orderBy($sort, $order); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('orderBy') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function dataAddOrderBy() { + return [ + ['column2', null, null, ['`column1` ASC', '`column2` ASC'], 'ORDER BY `column1` ASC, `column2` ASC'], + ['column2', null, 'ASC', ['`column1` ASC', '`column2` ASC'], 'ORDER BY `column1` ASC, `column2` ASC'], + ['column2', null, 'DESC', ['`column1` DESC', '`column2` ASC'], 'ORDER BY `column1` DESC, `column2` ASC'], + ['column2', 'ASC', null, ['`column1` ASC', '`column2` ASC'], 'ORDER BY `column1` ASC, `column2` ASC'], + ['column2', 'ASC', 'ASC', ['`column1` ASC', '`column2` ASC'], 'ORDER BY `column1` ASC, `column2` ASC'], + ['column2', 'ASC', 'DESC', ['`column1` DESC', '`column2` ASC'], 'ORDER BY `column1` DESC, `column2` ASC'], + ['column2', 'DESC', null, ['`column1` ASC', '`column2` DESC'], 'ORDER BY `column1` ASC, `column2` DESC'], + ['column2', 'DESC', 'ASC', ['`column1` ASC', '`column2` DESC'], 'ORDER BY `column1` ASC, `column2` DESC'], + ['column2', 'DESC', 'DESC', ['`column1` DESC', '`column2` DESC'], 'ORDER BY `column1` DESC, `column2` DESC'], + ]; + } + + /** + * @dataProvider dataAddOrderBy + * + * @param string $sort2 + * @param string $order2 + * @param string $order1 + * @param array $expectedQueryPart + * @param string $expectedQuery + */ + public function testAddOrderBy($sort2, $order2, $order1, $expectedQueryPart, $expectedQuery) { + $this->queryBuilder->orderBy('column1', $order1); + $this->queryBuilder->addOrderBy($sort2, $order2); + + $this->assertEquals( + $expectedQueryPart, + $this->queryBuilder->getQueryPart('orderBy') + ); + + $this->assertSame( + 'SELECT FROM ' . $expectedQuery, + $this->queryBuilder->getSQL() + ); + } + + public function testGetLastInsertId() { + $qB = $this->connection->getQueryBuilder(); + + try { + $qB->getLastInsertId(); + $this->fail('getLastInsertId() should throw an exception, when being called before insert()'); + } catch (\BadMethodCallException $e) { + $this->assertTrue(true); + } + + $qB->insert('properties') + ->values([ + 'userid' => $qB->expr()->literal('testFirstResult'), + 'propertypath' => $qB->expr()->literal('testing'), + 'propertyname' => $qB->expr()->literal('testing'), + 'propertyvalue' => $qB->expr()->literal('testing'), + ]) + ->execute(); + + $actual = $qB->getLastInsertId(); + + $this->assertNotNull($actual); + $this->assertInternalType('int', $actual); + $this->assertEquals($this->connection->lastInsertId('*PREFIX*properties'), $actual); + + $qB->delete('properties') + ->where($qB->expr()->eq('userid', $qB->expr()->literal('testFirstResult'))) + ->execute(); + + try { + $qB->getLastInsertId(); + $this->fail('getLastInsertId() should throw an exception, when being called after delete()'); + } catch (\BadMethodCallException $e) { + $this->assertTrue(true); + } + } + + public function dataGetTableName() { + return [ + ['*PREFIX*table', null, '`*PREFIX*table`'], + ['*PREFIX*table', true, '`*PREFIX*table`'], + ['*PREFIX*table', false, '`*PREFIX*table`'], + + ['table', null, '`*PREFIX*table`'], + ['table', true, '`*PREFIX*table`'], + ['table', false, '`table`'], + ]; + } + + /** + * @dataProvider dataGetTableName + * + * @param string $tableName + * @param bool $automatic + * @param string $expected + */ + public function testGetTableName($tableName, $automatic, $expected) { + if ($automatic !== null) { + $this->queryBuilder->automaticTablePrefix($automatic); + } + + $this->assertSame( + $expected, + $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) + ); + } +} diff --git a/tests/lib/DB/QueryBuilder/QuoteHelperTest.php b/tests/lib/DB/QueryBuilder/QuoteHelperTest.php new file mode 100644 index 00000000000..b83d9eed2df --- /dev/null +++ b/tests/lib/DB/QueryBuilder/QuoteHelperTest.php @@ -0,0 +1,145 @@ +<?php +/** + * @author Joas Schilling <nickvergessen@owncloud.com> + * + * @copyright Copyright (c) 2015, 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 <http://www.gnu.org/licenses/> + * + */ + +namespace Test\DB\QueryBuilder; + +use OC\DB\QueryBuilder\Literal; +use OC\DB\QueryBuilder\Parameter; +use OC\DB\QueryBuilder\QuoteHelper; +use OCP\DB\QueryBuilder\ILiteral; +use OCP\DB\QueryBuilder\IParameter; + +class QuoteHelperTest extends \Test\TestCase { + /** @var QuoteHelper */ + protected $helper; + + protected function setUp() { + parent::setUp(); + + $this->helper = new QuoteHelper(); + } + + public function dataQuoteColumnName() { + return [ + ['column', '`column`'], + [new Literal('literal'), 'literal'], + [new Literal(1), '1'], + [new Parameter(':param'), ':param'], + + // (string) 'null' is Doctrines way to set columns to null + // See https://github.com/owncloud/core/issues/19314 + ['null', 'null'], + ]; + } + + /** + * @dataProvider dataQuoteColumnName + * @param mixed $input + * @param string $expected + */ + public function testQuoteColumnName($input, $expected) { + $this->assertSame( + $expected, + $this->helper->quoteColumnName($input) + ); + } + + public function dataQuoteColumnNames() { + return [ + // Single case + ['d.column', 'd.`column`'], + ['column', '`column`'], + [new Literal('literal'), 'literal'], + [new Literal(1), '1'], + [new Parameter(':param'), ':param'], + + // Array case + [['column'], ['`column`']], + [[new Literal('literal')], ['literal']], + [[new Literal(1)], ['1']], + [[new Parameter(':param')], [':param']], + + // Array mixed cases + [['column1', 'column2'], ['`column1`', '`column2`']], + [['column', new Literal('literal')], ['`column`', 'literal']], + [['column', new Literal(1)], ['`column`', '1']], + [['column', new Parameter(':param')], ['`column`', ':param']], + ]; + } + + /** + * @dataProvider dataQuoteColumnNames + * @param mixed $input + * @param string $expected + */ + public function testQuoteColumnNames($input, $expected) { + $this->assertSame( + $expected, + $this->helper->quoteColumnNames($input) + ); + } + + /** + * @param array|string|ILiteral|IParameter $strings string, Literal or Parameter + * @return array|string + */ + public function quoteColumnNames($strings) { + if (!is_array($strings)) { + return $this->quoteColumnName($strings); + } + + $return = []; + foreach ($strings as $string) { + $return[] = $this->quoteColumnName($string); + } + + return $return; + } + + /** + * @param string|ILiteral|IParameter $string string, Literal or Parameter + * @return string + */ + public function quoteColumnName($string) { + if ($string instanceof IParameter) { + return $string->getName(); + } + + if ($string instanceof ILiteral) { + return $string->getLiteral(); + } + + if ($string === null) { + return $string; + } + + if (!is_string($string)) { + throw new \InvalidArgumentException('Only strings, Literals and Parameters are allowed'); + } + + if (substr_count($string, '.')) { + list($alias, $columnName) = explode('.', $string); + return '`' . $alias . '`.`' . $columnName . '`'; + } + + return '`' . $string . '`'; + } +} diff --git a/tests/lib/DB/SqliteMigrationTest.php b/tests/lib/DB/SqliteMigrationTest.php new file mode 100644 index 00000000000..76002e1afce --- /dev/null +++ b/tests/lib/DB/SqliteMigrationTest.php @@ -0,0 +1,49 @@ +<?php +/** + * Copyright (c) 2014 Thomas Müller <deepdiver@owncloud.com> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + +namespace Test\DB; + +/** + * Class SqliteMigration + * + * @group DB + */ +class SqliteMigrationTest extends \Test\TestCase { + + /** @var \Doctrine\DBAL\Connection */ + private $connection; + + /** @var string */ + private $tableName; + + protected function setUp() { + parent::setUp(); + + $this->connection = \OC::$server->getDatabaseConnection(); + if (!$this->connection->getDatabasePlatform() instanceof \Doctrine\DBAL\Platforms\SqlitePlatform) { + $this->markTestSkipped("Test only relevant on Sqlite"); + } + + $dbPrefix = \OC::$server->getConfig()->getSystemValue("dbtableprefix"); + $this->tableName = $this->getUniqueID($dbPrefix . '_enum_bit_test'); + $this->connection->exec("CREATE TABLE $this->tableName(t0 tinyint unsigned, t1 tinyint)"); + } + + protected function tearDown() { + $this->connection->getSchemaManager()->dropTable($this->tableName); + parent::tearDown(); + } + + public function testNonOCTables() { + $manager = new \OC\DB\MDB2SchemaManager($this->connection); + $manager->updateDbFromStructure(__DIR__ . '/testschema.xml'); + + $this->assertTrue(true); + } + +} diff --git a/tests/lib/DB/testschema.xml b/tests/lib/DB/testschema.xml new file mode 100644 index 00000000000..dfca920a0ef --- /dev/null +++ b/tests/lib/DB/testschema.xml @@ -0,0 +1,83 @@ +<?xml version="1.0" encoding="utf-8" ?> +<database> + + <name>*dbname*</name> + <create>true</create> + <overwrite>false</overwrite> + + <charset>utf8</charset> + + <table> + + <name>*dbprefix*table</name> + + <declaration> + <field> + <name>integerfield</name> + <type>integer</type> + <default>0</default> + <notnull>true</notnull> + <autoincrement>1</autoincrement> + <length>4</length> + </field> + <field> + <name>integerfield_default</name> + <type>integer</type> + <default>10</default> + <notnull>true</notnull> + <length>4</length> + </field> + <field> + <name>textfield</name> + <type>text</type> + <default>foo</default> + <notnull>true</notnull> + <length>32</length> + </field> + <field> + <name>clobfield</name> + <type>clob</type> + <notnull>true</notnull> + </field> + <field> + <name>booleanfield</name> + <type>boolean</type> + </field> + <field> + <name>booleanfield_true</name> + <type>boolean</type> + <default>true</default> + </field> + <field> + <name>booleanfield_false</name> + <type>boolean</type> + <default>false</default> + </field> + <field> + <name>decimalfield_precision_scale</name> + <type>decimal</type> + <precision>12</precision> + <scale>2</scale> + </field> + + <index> + <name>index_primary</name> + <primary>true</primary> + <unique>true</unique> + <field> + <name>integerfield</name> + <sorting>ascending</sorting> + </field> + </index> + + <index> + <name>index_boolean</name> + <unique>false</unique> + <field> + <name>booleanfield</name> + <sorting>ascending</sorting> + </field> + </index> + </declaration> + </table> +</database> diff --git a/tests/lib/DB/ts-autoincrement-after.xml b/tests/lib/DB/ts-autoincrement-after.xml new file mode 100644 index 00000000000..d4445f1e247 --- /dev/null +++ b/tests/lib/DB/ts-autoincrement-after.xml @@ -0,0 +1,32 @@ +<?xml version="1.0" encoding="utf-8" ?> +<database> + + <name>*dbname*</name> + <create>true</create> + <overwrite>false</overwrite> + + <charset>utf8</charset> + + <table> + + <name>*dbprefix*table</name> + + <declaration> + <field> + <name>auto_id</name> + <type>integer</type> + <default>0</default> + <notnull>true</notnull> + <autoincrement>1</autoincrement> + <length>4</length> + </field> + <field> + <name>textfield</name> + <type>text</type> + <default>foo</default> + <notnull>true</notnull> + <length>32</length> + </field> + </declaration> + </table> +</database> diff --git a/tests/lib/DB/ts-autoincrement-before.xml b/tests/lib/DB/ts-autoincrement-before.xml new file mode 100644 index 00000000000..412739e9a71 --- /dev/null +++ b/tests/lib/DB/ts-autoincrement-before.xml @@ -0,0 +1,24 @@ +<?xml version="1.0" encoding="utf-8" ?> +<database> + + <name>*dbname*</name> + <create>true</create> + <overwrite>false</overwrite> + + <charset>utf8</charset> + + <table> + + <name>*dbprefix*table</name> + + <declaration> + <field> + <name>textfield</name> + <type>text</type> + <default>foo</default> + <notnull>true</notnull> + <length>32</length> + </field> + </declaration> + </table> +</database> |