MigratorTest.php 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332
  1. <?php
  2. /**
  3. * Copyright (c) 2014 Robin Appelman <icewind@owncloud.com>
  4. * This file is licensed under the Affero General Public License version 3 or
  5. * later.
  6. * See the COPYING-README file.
  7. */
  8. namespace Test\DB;
  9. use Doctrine\DBAL\Exception;
  10. use Doctrine\DBAL\ParameterType;
  11. use Doctrine\DBAL\Platforms\MySQLPlatform;
  12. use Doctrine\DBAL\Platforms\OraclePlatform;
  13. use Doctrine\DBAL\Platforms\PostgreSQL94Platform;
  14. use Doctrine\DBAL\Platforms\SqlitePlatform;
  15. use Doctrine\DBAL\Schema\Schema;
  16. use Doctrine\DBAL\Schema\SchemaConfig;
  17. use OC\DB\Migrator;
  18. use OC\DB\MySQLMigrator;
  19. use OC\DB\OracleMigrator;
  20. use OC\DB\PostgreSqlMigrator;
  21. use OC\DB\SQLiteMigrator;
  22. use OCP\DB\Types;
  23. use OCP\IConfig;
  24. /**
  25. * Class MigratorTest
  26. *
  27. * @group DB
  28. *
  29. * @package Test\DB
  30. */
  31. class MigratorTest extends \Test\TestCase {
  32. /**
  33. * @var \Doctrine\DBAL\Connection $connection
  34. */
  35. private $connection;
  36. /**
  37. * @var IConfig
  38. **/
  39. private $config;
  40. /** @var string */
  41. private $tableName;
  42. /** @var string */
  43. private $tableNameTmp;
  44. protected function setUp(): void {
  45. parent::setUp();
  46. $this->config = \OC::$server->getConfig();
  47. $this->connection = \OC::$server->get(\OC\DB\Connection::class);
  48. $this->tableName = $this->getUniqueTableName();
  49. $this->tableNameTmp = $this->getUniqueTableName();
  50. }
  51. private function getMigrator(): Migrator {
  52. $platform = $this->connection->getDatabasePlatform();
  53. $random = \OC::$server->getSecureRandom();
  54. $dispatcher = \OC::$server->get(\OCP\EventDispatcher\IEventDispatcher::class);
  55. if ($platform instanceof SqlitePlatform) {
  56. return new SQLiteMigrator($this->connection, $this->config, $dispatcher);
  57. } elseif ($platform instanceof OraclePlatform) {
  58. return new OracleMigrator($this->connection, $this->config, $dispatcher);
  59. } elseif ($platform instanceof MySQLPlatform) {
  60. return new MySQLMigrator($this->connection, $this->config, $dispatcher);
  61. } elseif ($platform instanceof PostgreSQL94Platform) {
  62. return new PostgreSqlMigrator($this->connection, $this->config, $dispatcher);
  63. }
  64. return new Migrator($this->connection, $this->config, $dispatcher);
  65. }
  66. private function getUniqueTableName() {
  67. return strtolower($this->getUniqueID($this->config->getSystemValueString('dbtableprefix', 'oc_') . 'test_'));
  68. }
  69. protected function tearDown(): void {
  70. // Try to delete if exists (IF EXISTS NOT SUPPORTED IN ORACLE)
  71. try {
  72. $this->connection->exec('DROP TABLE ' . $this->connection->quoteIdentifier($this->tableNameTmp));
  73. } catch (Exception $e) {
  74. }
  75. try {
  76. $this->connection->exec('DROP TABLE ' . $this->connection->quoteIdentifier($this->tableName));
  77. } catch (Exception $e) {
  78. }
  79. parent::tearDown();
  80. }
  81. /**
  82. * @return \Doctrine\DBAL\Schema\Schema[]
  83. */
  84. private function getDuplicateKeySchemas() {
  85. $startSchema = new Schema([], [], $this->getSchemaConfig());
  86. $table = $startSchema->createTable($this->tableName);
  87. $table->addColumn('id', 'integer');
  88. $table->addColumn('name', 'string');
  89. $table->addIndex(['id'], $this->tableName . '_id');
  90. $endSchema = new Schema([], [], $this->getSchemaConfig());
  91. $table = $endSchema->createTable($this->tableName);
  92. $table->addColumn('id', 'integer');
  93. $table->addColumn('name', 'string');
  94. $table->addUniqueIndex(['id'], $this->tableName . '_id');
  95. return [$startSchema, $endSchema];
  96. }
  97. /**
  98. * @return \Doctrine\DBAL\Schema\Schema[]
  99. */
  100. private function getChangedTypeSchema($from, $to) {
  101. $startSchema = new Schema([], [], $this->getSchemaConfig());
  102. $table = $startSchema->createTable($this->tableName);
  103. $table->addColumn('id', $from);
  104. $table->addColumn('name', 'string');
  105. $table->addIndex(['id'], $this->tableName . '_id');
  106. $endSchema = new Schema([], [], $this->getSchemaConfig());
  107. $table = $endSchema->createTable($this->tableName);
  108. $table->addColumn('id', $to);
  109. $table->addColumn('name', 'string');
  110. $table->addIndex(['id'], $this->tableName . '_id');
  111. return [$startSchema, $endSchema];
  112. }
  113. private function getSchemaConfig() {
  114. $config = new SchemaConfig();
  115. $config->setName($this->connection->getDatabase());
  116. return $config;
  117. }
  118. private function isSQLite() {
  119. return $this->connection->getDatabasePlatform() instanceof SqlitePlatform;
  120. }
  121. private function isMySQL() {
  122. return $this->connection->getDatabasePlatform() instanceof MySQLPlatform;
  123. }
  124. public function testUpgrade() {
  125. [$startSchema, $endSchema] = $this->getDuplicateKeySchemas();
  126. $migrator = $this->getMigrator();
  127. $migrator->migrate($startSchema);
  128. $this->connection->insert($this->tableName, ['id' => 1, 'name' => 'foo']);
  129. $this->connection->insert($this->tableName, ['id' => 2, 'name' => 'bar']);
  130. $this->connection->insert($this->tableName, ['id' => 3, 'name' => 'qwerty']);
  131. $migrator->migrate($endSchema);
  132. $this->addToAssertionCount(1);
  133. }
  134. public function testUpgradeDifferentPrefix() {
  135. $oldTablePrefix = $this->config->getSystemValueString('dbtableprefix', 'oc_');
  136. $this->config->setSystemValue('dbtableprefix', 'ownc_');
  137. $this->tableName = strtolower($this->getUniqueID($this->config->getSystemValueString('dbtableprefix') . 'test_'));
  138. [$startSchema, $endSchema] = $this->getDuplicateKeySchemas();
  139. $migrator = $this->getMigrator();
  140. $migrator->migrate($startSchema);
  141. $this->connection->insert($this->tableName, ['id' => 1, 'name' => 'foo']);
  142. $this->connection->insert($this->tableName, ['id' => 2, 'name' => 'bar']);
  143. $this->connection->insert($this->tableName, ['id' => 3, 'name' => 'qwerty']);
  144. $migrator->migrate($endSchema);
  145. $this->addToAssertionCount(1);
  146. $this->config->setSystemValue('dbtableprefix', $oldTablePrefix);
  147. }
  148. public function testInsertAfterUpgrade() {
  149. [$startSchema, $endSchema] = $this->getDuplicateKeySchemas();
  150. $migrator = $this->getMigrator();
  151. $migrator->migrate($startSchema);
  152. $migrator->migrate($endSchema);
  153. $this->connection->insert($this->tableName, ['id' => 1, 'name' => 'foo']);
  154. $this->connection->insert($this->tableName, ['id' => 2, 'name' => 'bar']);
  155. try {
  156. $this->connection->insert($this->tableName, ['id' => 2, 'name' => 'qwerty']);
  157. $this->fail('Expected duplicate key insert to fail');
  158. } catch (Exception $e) {
  159. $this->addToAssertionCount(1);
  160. }
  161. }
  162. public function testAddingPrimaryKeyWithAutoIncrement() {
  163. $startSchema = new Schema([], [], $this->getSchemaConfig());
  164. $table = $startSchema->createTable($this->tableName);
  165. $table->addColumn('id', 'integer');
  166. $table->addColumn('name', 'string');
  167. $endSchema = new Schema([], [], $this->getSchemaConfig());
  168. $table = $endSchema->createTable($this->tableName);
  169. $table->addColumn('id', 'integer', ['autoincrement' => true]);
  170. $table->addColumn('name', 'string');
  171. $table->setPrimaryKey(['id']);
  172. $migrator = $this->getMigrator();
  173. $migrator->migrate($startSchema);
  174. $migrator->migrate($endSchema);
  175. $this->addToAssertionCount(1);
  176. }
  177. public function testReservedKeywords() {
  178. $startSchema = new Schema([], [], $this->getSchemaConfig());
  179. $table = $startSchema->createTable($this->tableName);
  180. $table->addColumn('id', 'integer', ['autoincrement' => true]);
  181. $table->addColumn('user', 'string', ['length' => 255]);
  182. $table->setPrimaryKey(['id']);
  183. $endSchema = new Schema([], [], $this->getSchemaConfig());
  184. $table = $endSchema->createTable($this->tableName);
  185. $table->addColumn('id', 'integer', ['autoincrement' => true]);
  186. $table->addColumn('user', 'string', ['length' => 64]);
  187. $table->setPrimaryKey(['id']);
  188. $migrator = $this->getMigrator();
  189. $migrator->migrate($startSchema);
  190. $migrator->migrate($endSchema);
  191. $this->addToAssertionCount(1);
  192. }
  193. /**
  194. * Test for nextcloud/server#36803
  195. */
  196. public function testColumnCommentsInUpdate() {
  197. $startSchema = new Schema([], [], $this->getSchemaConfig());
  198. $table = $startSchema->createTable($this->tableName);
  199. $table->addColumn('id', 'integer', ['autoincrement' => true, 'comment' => 'foo']);
  200. $table->setPrimaryKey(['id']);
  201. $endSchema = new Schema([], [], $this->getSchemaConfig());
  202. $table = $endSchema->createTable($this->tableName);
  203. $table->addColumn('id', 'integer', ['autoincrement' => true, 'comment' => 'foo']);
  204. // Assert adding comments on existing tables work (or at least does not throw)
  205. $table->addColumn('time', 'integer', ['comment' => 'unix-timestamp', 'notnull' => false]);
  206. $table->setPrimaryKey(['id']);
  207. $migrator = $this->getMigrator();
  208. $migrator->migrate($startSchema);
  209. $migrator->migrate($endSchema);
  210. $this->addToAssertionCount(1);
  211. }
  212. public function testAddingForeignKey() {
  213. $startSchema = new Schema([], [], $this->getSchemaConfig());
  214. $table = $startSchema->createTable($this->tableName);
  215. $table->addColumn('id', 'integer', ['autoincrement' => true]);
  216. $table->addColumn('name', 'string');
  217. $table->setPrimaryKey(['id']);
  218. $fkName = "fkc";
  219. $tableFk = $startSchema->createTable($this->tableNameTmp);
  220. $tableFk->addColumn('fk_id', 'integer');
  221. $tableFk->addColumn('name', 'string');
  222. $tableFk->addForeignKeyConstraint($this->tableName, ['fk_id'], ['id'], [], $fkName);
  223. $migrator = $this->getMigrator();
  224. $migrator->migrate($startSchema);
  225. $this->assertTrue($startSchema->getTable($this->tableNameTmp)->hasForeignKey($fkName));
  226. }
  227. public function dataNotNullEmptyValuesFailOracle(): array {
  228. return [
  229. [ParameterType::BOOLEAN, true, Types::BOOLEAN, false],
  230. [ParameterType::BOOLEAN, false, Types::BOOLEAN, true],
  231. [ParameterType::STRING, 'foo', Types::STRING, false],
  232. [ParameterType::STRING, '', Types::STRING, true],
  233. [ParameterType::INTEGER, 1234, Types::INTEGER, false],
  234. [ParameterType::INTEGER, 0, Types::INTEGER, false], // Integer 0 is not stored as Null and therefor works
  235. [ParameterType::STRING, '{"a": 2}', Types::JSON, false],
  236. ];
  237. }
  238. /**
  239. * @dataProvider dataNotNullEmptyValuesFailOracle
  240. *
  241. * @param int $parameterType
  242. * @param bool|int|string $value
  243. * @param string $columnType
  244. * @param bool $oracleThrows
  245. */
  246. public function testNotNullEmptyValuesFailOracle(int $parameterType, $value, string $columnType, bool $oracleThrows): void {
  247. $startSchema = new Schema([], [], $this->getSchemaConfig());
  248. $table = $startSchema->createTable($this->tableName);
  249. $table->addColumn('id', Types::BIGINT);
  250. $table->addColumn('will_it_blend', $columnType, [
  251. 'notnull' => true,
  252. ]);
  253. $table->addIndex(['id'], $this->tableName . '_id');
  254. $migrator = $this->getMigrator();
  255. $migrator->migrate($startSchema);
  256. if ($oracleThrows && $this->connection->getDatabasePlatform() instanceof OraclePlatform) {
  257. // Oracle can not store false|empty string in notnull columns
  258. $this->expectException(\Doctrine\DBAL\Exception\NotNullConstraintViolationException::class);
  259. }
  260. $this->connection->insert(
  261. $this->tableName,
  262. ['id' => 1, 'will_it_blend' => $value],
  263. ['id' => ParameterType::INTEGER, 'will_it_blend' => $parameterType],
  264. );
  265. $this->addToAssertionCount(1);
  266. }
  267. }