Various database migration fixes (#25209)

* String columns with a length higher then 4000 are converted into a CLOB columns automagically - we have to respect this when migrating

* Adding schema migration tests to prevent unnecessary and non-sense migration steps
Fix Oracle autoincrement and unsigned handling

* Fix sqlite integer type for autoincrement

* Use lower case table names - fixes pg

* Fix postgres with default -1 - this only affect pg 9.4 servers - 9.5 seems to work fine

5 files changed, 93 insertions, 1 deletions

diff --git a/lib/private/DB/MDB2SchemaManager.php b/lib/private/DB/MDB2SchemaManager.php
index 1d25ae9f18c..79811d8c6cd 100644
--- a/lib/private/DB/MDB2SchemaManager.php
+++ b/lib/private/DB/MDB2SchemaManager.php
@@ -84,7 +84,7 @@ class MDB2SchemaManager {
 		} else if ($platform instanceof MySqlPlatform) {
 			return new MySQLMigrator($this->conn, $random, $config, $dispatcher);
 		} else if ($platform instanceof PostgreSqlPlatform) {
-			return new Migrator($this->conn, $random, $config, $dispatcher);
+			return new PostgreSqlMigrator($this->conn, $random, $config, $dispatcher);
 		} else {
 			return new NoCheckMigrator($this->conn, $random, $config, $dispatcher);
 		}
diff --git a/lib/private/DB/Migrator.php b/lib/private/DB/Migrator.php
index 8b8a34d9865..f2efd6916d7 100644
--- a/lib/private/DB/Migrator.php
+++ b/lib/private/DB/Migrator.php
@@ -33,6 +33,8 @@ use \Doctrine\DBAL\Schema\Table;
 use \Doctrine\DBAL\Schema\Schema;
 use \Doctrine\DBAL\Schema\SchemaConfig;
 use \Doctrine\DBAL\Schema\Comparator;
+use Doctrine\DBAL\Types\StringType;
+use Doctrine\DBAL\Types\Type;
 use OCP\IConfig;
 use OCP\Security\ISecureRandom;
 use Symfony\Component\EventDispatcher\EventDispatcher;
@@ -194,7 +196,25 @@ class Migrator {
 		return new Table($newName, $table->getColumns(), $newIndexes, array(), 0, $table->getOptions());
 	}
 
+	/**
+	 * @param Schema $targetSchema
+	 * @param \Doctrine\DBAL\Connection $connection
+	 * @return \Doctrine\DBAL\Schema\SchemaDiff
+	 * @throws DBALException
+	 */
 	protected function getDiff(Schema $targetSchema, \Doctrine\DBAL\Connection $connection) {
+		// adjust varchar columns with a length higher then getVarcharMaxLength to clob
+		foreach ($targetSchema->getTables() as $table) {
+			foreach ($table->getColumns() as $column) {
+				if ($column->getType() instanceof StringType) {
+					if ($column->getLength() > $connection->getDatabasePlatform()->getVarcharMaxLength()) {
+						$column->setType(Type::getType('text'));
+						$column->setLength(null);
+					}
+				}
+			}
+		}
+
 		$filterExpression = $this->getFilterExpression();
 		$this->connection->getConfiguration()->
 		setFilterSchemaAssetsExpression($filterExpression);
diff --git a/lib/private/DB/OracleMigrator.php b/lib/private/DB/OracleMigrator.php
index ceb89cf64d4..010f9213a1d 100644
--- a/lib/private/DB/OracleMigrator.php
+++ b/lib/private/DB/OracleMigrator.php
@@ -23,6 +23,7 @@
 
 namespace OC\DB;
 
+use Doctrine\DBAL\Schema\ColumnDiff;
 use Doctrine\DBAL\Schema\Schema;
 
 class OracleMigrator extends NoCheckMigrator {
@@ -39,7 +40,12 @@ class OracleMigrator extends NoCheckMigrator {
 			$tableDiff->name = $this->connection->quoteIdentifier($tableDiff->name);
 			foreach ($tableDiff->changedColumns as $column) {
 				$column->oldColumnName = $this->connection->quoteIdentifier($column->oldColumnName);
+				// auto increment is not relevant for oracle and can anyhow not be applied on change
+				$column->changedProperties = array_diff($column->changedProperties, ['autoincrement', 'unsigned']);
 			}
+			$tableDiff->changedColumns = array_filter($tableDiff->changedColumns, function (ColumnDiff $column) {
+				return count($column->changedProperties) > 0;
+			});
 		}
 
 		return $schemaDiff;
diff --git a/lib/private/DB/PostgreSqlMigrator.php b/lib/private/DB/PostgreSqlMigrator.php
new file mode 100644
index 00000000000..75e6e0a16c9
--- /dev/null
+++ b/lib/private/DB/PostgreSqlMigrator.php
@@ -0,0 +1,55 @@
+<?php
+/**
+ * @author Thomas Müller <thomas.mueller@tmit.eu>
+ *
+ * @copyright Copyright (c) 2016, ownCloud, Inc.
+ * @license AGPL-3.0
+ *
+ * This code is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License, version 3,
+ * as published by the Free Software Foundation.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License, version 3,
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>
+ *
+ */
+
+namespace OC\DB;
+
+use Doctrine\DBAL\Schema\Schema;
+
+class PostgreSqlMigrator extends Migrator {
+	/**
+	 * @param Schema $targetSchema
+	 * @param \Doctrine\DBAL\Connection $connection
+	 * @return \Doctrine\DBAL\Schema\SchemaDiff
+	 */
+	protected function getDiff(Schema $targetSchema, \Doctrine\DBAL\Connection $connection) {
+		$schemaDiff = parent::getDiff($targetSchema, $connection);
+
+		foreach ($schemaDiff->changedTables as $tableDiff) {
+			// fix default value in brackets - pg 9.4 is returning a negative default value in ()
+			// see https://github.com/doctrine/dbal/issues/2427
+			foreach ($tableDiff->changedColumns as $column) {
+				$column->changedProperties = array_filter($column->changedProperties, function ($changedProperties) use ($column) {
+					if ($changedProperties !== 'default') {
+						return true;
+					}
+					$fromDefault = $column->fromColumn->getDefault();
+					$toDefault = $column->column->getDefault();
+					$fromDefault = trim($fromDefault, "()");
+
+					// by intention usage of !=
+					return $fromDefault != $toDefault;
+				});
+			}
+		}
+
+		return $schemaDiff;
+	}
+}
diff --git a/lib/private/DB/SQLiteMigrator.php b/lib/private/DB/SQLiteMigrator.php
index 8ea32581011..837a3a2987f 100644
--- a/lib/private/DB/SQLiteMigrator.php
+++ b/lib/private/DB/SQLiteMigrator.php
@@ -26,6 +26,8 @@ namespace OC\DB;
 
 use Doctrine\DBAL\DBALException;
 use Doctrine\DBAL\Schema\Schema;
+use Doctrine\DBAL\Types\BigIntType;
+use Doctrine\DBAL\Types\Type;
 
 class SQLiteMigrator extends Migrator {
 
@@ -76,6 +78,15 @@ class SQLiteMigrator extends Migrator {
 		$platform->registerDoctrineTypeMapping('smallint unsigned', 'integer');
 		$platform->registerDoctrineTypeMapping('varchar ', 'string');
 
+		// with sqlite autoincrement columns is of type integer
+		foreach ($targetSchema->getTables() as $table) {
+			foreach ($table->getColumns() as $column) {
+				if ($column->getType() instanceof BigIntType && $column->getAutoincrement()) {
+					$column->setType(Type::getType('integer'));
+				}
+			}
+		}
+
 		return parent::getDiff($targetSchema, $connection);
 	}
 }