Exemple #1
0
        public static void AlterComments(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
        {
            if (oldSchema == null)
            {
                return;
            }

            foreach (var oldTable in oldSchema.GetTables())
            {
                var newTable = newSchema.GetTable(oldTable.Name);

                if (newTable == null)
                {
                    continue;
                }

                foreach (var oldTrigger in oldTable.GetTriggers())
                {
                    var newTrigger =
                        newTable.GetTrigger(oldTrigger.Name);

                    if (newTrigger == null)
                    {
                        continue;
                    }

                    if (oldTrigger.Comment == null &&
                        newTrigger.Comment != null ||
                        oldTrigger.Comment != null &&
                        newTrigger.Comment != null &&
                        !oldTrigger.Comment.Equals(
                            newTrigger.Comment))
                    {
                        searchPathHelper.OutputSearchPath(writer);
                        writer.WriteLine();
                        writer.Write("COMMENT ON TRIGGER ");
                        writer.Write(PgDiffUtils.GetQuotedName(newTrigger.Name));
                        writer.Write(" ON ");
                        writer.Write(PgDiffUtils.GetQuotedName(newTrigger.TableName));
                        writer.Write(" IS ");
                        writer.Write(newTrigger.Comment);
                        writer.WriteLine(';');
                    }
                    else if (oldTrigger.Comment != null && newTrigger.Comment == null)
                    {
                        searchPathHelper.OutputSearchPath(writer);
                        writer.WriteLine();
                        writer.Write("COMMENT ON TRIGGER ");
                        writer.Write(PgDiffUtils.GetQuotedName(newTrigger.Name));
                        writer.Write(" ON ");
                        writer.Write(PgDiffUtils.GetQuotedName(newTrigger.TableName));
                        writer.WriteLine(" IS NULL;");
                    }
                }
            }
        }
Exemple #2
0
 private static void AddDropTableColumns(List <string> statements, PgTable oldTable, PgTable newTable)
 {
     foreach (var column in oldTable.Columns)
     {
         if (!newTable.ContainsColumn(column.Name))
         {
             statements.Add("\tDROP COLUMN " + PgDiffUtils.GetQuotedName(column.Name));
         }
     }
 }
Exemple #3
0
        private static void AlterComments(TextWriter writer, PgTable oldTable, PgTable newTable, SearchPathHelper searchPathHelper)
        {
            if (oldTable.Comment == null &&
                newTable.Comment != null ||
                oldTable.Comment != null &&
                newTable.Comment != null &&
                !oldTable.Comment.Equals(newTable.Comment))
            {
                searchPathHelper.OutputSearchPath(writer);
                writer.WriteLine();
                writer.Write("COMMENT ON TABLE ");
                writer.Write(PgDiffUtils.GetQuotedName(newTable.Name));
                writer.Write(" IS ");
                writer.Write(newTable.Comment);
                writer.WriteLine(';');
            }
            else if (oldTable.Comment != null && newTable.Comment == null)
            {
                searchPathHelper.OutputSearchPath(writer);
                writer.WriteLine();
                writer.Write("COMMENT ON TABLE ");
                writer.Write(PgDiffUtils.GetQuotedName(newTable.Name));
                writer.WriteLine(" IS NULL;");
            }

            foreach (var newColumn in newTable.Columns)
            {
                var oldColumn  = oldTable.GetColumn(newColumn.Name);
                var oldComment = oldColumn?.Comment;
                var newComment = newColumn.Comment;

                if (newComment != null && (!oldComment?.Equals(newComment) ?? newComment != null))
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("COMMENT ON COLUMN ");
                    writer.Write(PgDiffUtils.GetQuotedName(newTable.Name));
                    writer.Write('.');
                    writer.Write(PgDiffUtils.GetQuotedName(newColumn.Name));
                    writer.Write(" IS ");
                    writer.Write(newColumn.Comment);
                    writer.WriteLine(';');
                }
                else if (oldComment != null && newComment == null)
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("COMMENT ON COLUMN ");
                    writer.Write(PgDiffUtils.GetQuotedName(newTable.Name));
                    writer.Write('.');
                    writer.Write(PgDiffUtils.GetQuotedName(newColumn.Name));
                    writer.WriteLine(" IS NULL;");
                }
            }
        }
Exemple #4
0
 private static void DropOldSchemas(TextWriter writer, PgDatabase oldDatabase, PgDatabase newDatabase)
 {
     foreach (var oldSchema in oldDatabase.Schemas)
     {
         if (newDatabase.GetSchema(oldSchema.Name) == null)
         {
             writer.WriteLine();
             writer.WriteLine($"DROP SCHEMA {PgDiffUtils.GetQuotedName(oldSchema.Name)} CASCADE;");
         }
     }
 }
Exemple #5
0
        private static void AddModifyTableColumns(List <string> statements, PgDiffArguments arguments,
                                                  PgTable oldTable, PgTable newTable, List <PgColumn> dropDefaultsColumns)
        {
            foreach (var newColumn in newTable.Columns)
            {
                if (!oldTable.ContainsColumn(newColumn.Name))
                {
                    continue;
                }

                var oldColumn     = oldTable.GetColumn(newColumn.Name);
                var newColumnName = PgDiffUtils.GetQuotedName(newColumn.Name);

                if (!oldColumn.Type.Equals(newColumn.Type))
                {
                    statements.Add("\tALTER COLUMN " + newColumnName + " TYPE "
                                   + newColumn.Type + " /* "
                                   + string.Format(Resources.TypeParameterChange, newTable.Name, oldColumn.Type, newColumn.Type) + " */");
                }

                var oldDefault = oldColumn.DefaultValue ?? "";
                var newDefault = newColumn.DefaultValue ?? "";

                if (!oldDefault.Equals(newDefault))
                {
                    statements.Add(newDefault.Length == 0
                        ? $"\tALTER COLUMN {newColumnName} DROP DEFAULT"
                        : $"\tALTER COLUMN {newColumnName} SET DEFAULT {newDefault}");
                }

                if (oldColumn.NullValue == newColumn.NullValue)
                {
                    continue;
                }

                if (newColumn.NullValue)
                {
                    statements.Add($"\tALTER COLUMN {newColumnName} DROP NOT NULL");
                }
                else
                {
                    if (arguments.AddDefaults)
                    {
                        var defaultValue = PgColumnUtils.GetDefaultValue(newColumn.Type);
                        if (defaultValue != null)
                        {
                            statements.Add($"\tALTER COLUMN {newColumnName} SET DEFAULT {defaultValue}");
                            dropDefaultsColumns.Add(newColumn);
                        }
                    }
                    statements.Add($"\tALTER COLUMN {newColumnName} SET NOT NULL");
                }
            }
        }
Exemple #6
0
        private static void CheckTablespace(TextWriter writer, PgTable oldTable, PgTable newTable, SearchPathHelper searchPathHelper)
        {
            if (oldTable.Tablespace == null && newTable.Tablespace == null ||
                oldTable.Tablespace != null &&
                oldTable.Tablespace.Equals(newTable.Tablespace))
            {
                return;
            }

            searchPathHelper.OutputSearchPath(writer);
            writer.WriteLine();
            writer.WriteLine("ALTER TABLE " + PgDiffUtils.GetQuotedName(newTable.Name));
            writer.WriteLine("\tTABLESPACE " + newTable.Tablespace + ';');
        }
Exemple #7
0
        private static void AddAlterStatistics(TextWriter writer, PgTable oldTable, PgTable newTable, SearchPathHelper searchPathHelper)
        {
            var stats = new Dictionary <string, int?>();

            foreach (var newColumn in newTable.Columns)
            {
                var oldColumn = oldTable.GetColumn(newColumn.Name);

                if (oldColumn != null)
                {
                    var oldStat      = oldColumn.Statistics;
                    var newStat      = newColumn.Statistics;
                    int?newStatValue = null;

                    if (newStat != null && (oldStat == null || !newStat.Equals(oldStat)))
                    {
                        newStatValue = newStat;
                    }
                    else if (oldStat != null && newStat == null)
                    {
                        newStatValue = -1;
                    }

                    if (newStatValue != null)
                    {
                        stats.Add(newColumn.Name, newStatValue);
                    }
                }
            }

            foreach (var entry in stats)
            {
                searchPathHelper.OutputSearchPath(writer);
                writer.WriteLine();
                writer.Write("ALTER TABLE ONLY ");
                writer.Write(PgDiffUtils.GetQuotedName(newTable.Name));
                writer.Write(" ALTER COLUMN ");
                writer.Write(PgDiffUtils.GetQuotedName(entry.Key));
                writer.Write(" SET STATISTICS ");
                writer.Write(entry.Value);
                writer.WriteLine(';');
            }
        }
Exemple #8
0
        private static void UpdateTableColumns(TextWriter writer, PgDiffArguments arguments,
                                               PgTable oldTable, PgTable newTable, SearchPathHelper searchPathHelper)
        {
            var statements = new List <string>();

            var dropDefaultsColumns = new List <PgColumn>();

            AddDropTableColumns(statements, oldTable, newTable);
            AddCreateTableColumns(
                statements, arguments, oldTable, newTable, dropDefaultsColumns);
            AddModifyTableColumns(
                statements, arguments, oldTable, newTable, dropDefaultsColumns);

            if (statements.Count > 0)
            {
                var quotedTableName =
                    PgDiffUtils.GetQuotedName(newTable.Name);
                searchPathHelper.OutputSearchPath(writer);
                writer.WriteLine();
                writer.WriteLine("ALTER TABLE " + quotedTableName);

                for (var i = 0; i < statements.Count; i++)
                {
                    writer.Write(statements[i]);
                    writer.WriteLine(i + 1 < statements.Count ? "," : ";");
                }

                if (dropDefaultsColumns.Count > 0)
                {
                    writer.WriteLine();
                    writer.WriteLine("ALTER TABLE " + quotedTableName);

                    for (var i = 0; i < dropDefaultsColumns.Count; i++)
                    {
                        writer.Write("\tALTER COLUMN ");
                        writer.Write(PgDiffUtils.GetQuotedName(dropDefaultsColumns[i].Name));
                        writer.Write(" DROP DEFAULT");
                        writer.WriteLine(i + 1 < dropDefaultsColumns.Count ? "," : ";");
                    }
                }
            }
        }
Exemple #9
0
        public static void DropClusters(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
        {
            foreach (var newTable in newSchema.GetTables())
            {
                var oldTable = oldSchema?.GetTable(newTable.Name);

                var oldCluster = oldTable?.ClusterIndexName;

                var newCluster = newTable.ClusterIndexName;

                if (oldCluster != null && newCluster == null && newTable.ContainsIndex(oldCluster))
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("ALTER TABLE ");
                    writer.Write(PgDiffUtils.GetQuotedName(newTable.Name));
                    writer.WriteLine(" SET WITHOUT CLUSTER;");
                }
            }
        }
Exemple #10
0
        public static void AlterComments(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
        {
            if (oldSchema == null)
            {
                return;
            }

            foreach (var oldIndex in oldSchema.GetIndexes())
            {
                var newIndex = newSchema.GetIndex(oldIndex.Name);

                if (newIndex == null)
                {
                    continue;
                }

                if (oldIndex.Comment == null &&
                    newIndex.Comment != null ||
                    oldIndex.Comment != null &&
                    newIndex.Comment != null &&
                    !oldIndex.Comment.Equals(
                        newIndex.Comment))
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("COMMENT ON INDEX ");
                    writer.Write(PgDiffUtils.GetQuotedName(newIndex.Name));
                    writer.Write(" IS ");
                    writer.Write(newIndex.Comment);
                    writer.WriteLine(';');
                }
                else if (oldIndex.Comment != null && newIndex.Comment == null)
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("COMMENT ON INDEX ");
                    writer.Write(PgDiffUtils.GetQuotedName(newIndex.Name));
                    writer.WriteLine(" IS NULL;");
                }
            }
        }
Exemple #11
0
        public static void CreateClusters(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
        {
            foreach (var newTable in newSchema.GetTables())
            {
                var oldTable   = oldSchema?.GetTable(newTable.Name);
                var oldCluster = oldTable?.ClusterIndexName;

                var newCluster = newTable.ClusterIndexName;

                if (oldCluster == null && newCluster != null ||
                    oldCluster != null && newCluster != null &&
                    newCluster.CompareTo(oldCluster) != 0)
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("ALTER TABLE ");
                    writer.Write(PgDiffUtils.GetQuotedName(newTable.Name));
                    writer.Write(" CLUSTER ON ");
                    writer.Write(PgDiffUtils.GetQuotedName(newCluster));
                    writer.WriteLine(';');
                }
            }
        }
Exemple #12
0
        private static void AddAlterStorage(TextWriter writer, PgTable oldTable, PgTable newTable, SearchPathHelper searchPathHelper)
        {
            foreach (var newColumn in newTable.Columns)
            {
                var oldColumn  = oldTable.GetColumn(newColumn.Name);
                var oldStorage = string.IsNullOrEmpty(oldColumn?.Storage)
                    ? null
                    : oldColumn.Storage;
                var newStorage = string.IsNullOrEmpty(newColumn.Storage)
                    ? null
                    : newColumn.Storage;

                if (newStorage == null && oldStorage != null)
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.WriteLine(Resources.WarningUnableToDetermineStorageType, newTable.Name + '.' + newColumn.Name);

                    continue;
                }

                if (newStorage == null || newStorage.EqualsIgnoreCase(oldStorage))
                {
                    continue;
                }

                searchPathHelper.OutputSearchPath(writer);
                writer.WriteLine();
                writer.Write("ALTER TABLE ONLY ");
                writer.Write(PgDiffUtils.GetQuotedName(newTable.Name));
                writer.Write(" ALTER COLUMN ");
                writer.Write(PgDiffUtils.GetQuotedName(newColumn.Name));
                writer.Write(" SET STORAGE ");
                writer.Write(newStorage);
                writer.Write(';');
            }
        }
Exemple #13
0
        private static void CheckInherits(TextWriter writer, PgTable oldTable, PgTable newTable, SearchPathHelper searchPathHelper)
        {
            foreach (var tableName in oldTable.GetInherits())
            {
                if (!newTable.GetInherits().Contains(tableName))
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.WriteLine("ALTER TABLE " + PgDiffUtils.GetQuotedName(newTable.Name));
                    writer.WriteLine("\tNO INHERIT " + PgDiffUtils.GetQuotedName(tableName) + ';');
                }
            }

            foreach (var tableName in newTable.GetInherits())
            {
                if (!oldTable.GetInherits().Contains(tableName))
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.WriteLine("ALTER TABLE " + PgDiffUtils.GetQuotedName(newTable.Name));
                    writer.WriteLine("\tINHERIT " + PgDiffUtils.GetQuotedName(tableName) + ';');
                }
            }
        }
Exemple #14
0
        private static void CheckWithOids(TextWriter writer, PgTable oldTable, PgTable newTable, SearchPathHelper searchPathHelper)
        {
            if (oldTable.With == null && newTable.With == null || oldTable.With != null && oldTable.With.Equals(newTable.With))
            {
                return;
            }

            searchPathHelper.OutputSearchPath(writer);
            writer.WriteLine();
            writer.WriteLine("ALTER TABLE " + PgDiffUtils.GetQuotedName(newTable.Name));

            if (newTable.With == null || "OIDS=false".EqualsIgnoreCase(newTable.With))
            {
                writer.WriteLine("\tSET WITHOUT OIDS;");
            }
            else if ("OIDS".EqualsIgnoreCase(newTable.With) || "OIDS=true".EqualsIgnoreCase(newTable.With))
            {
                writer.WriteLine("\tSET WITH OIDS;");
            }
            else
            {
                writer.WriteLine("\tSET " + newTable.With + ";");
            }
        }
Exemple #15
0
        public static void AlterComments(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
        {
            if (oldSchema == null)
            {
                return;
            }

            foreach (var oldFun in oldSchema.GetFunctions())
            {
                var newFunction = newSchema.GetFunction(oldFun.GetSignature());

                if (newFunction == null)
                {
                    continue;
                }

                if (oldFun.Comment == null &&
                    newFunction.Comment != null ||
                    oldFun.Comment != null &&
                    newFunction.Comment != null &&
                    !oldFun.Comment.Equals(
                        newFunction.Comment))
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("COMMENT ON FUNCTION ");
                    writer.Write(PgDiffUtils.GetQuotedName(newFunction.Name));
                    writer.Write('(');

                    var addComma = false;

                    foreach (var argument in newFunction.GetArguments())
                    {
                        if (addComma)
                        {
                            writer.Write(", ");
                        }
                        else
                        {
                            addComma = true;
                        }

                        writer.Write(argument.GetDeclaration(false));
                    }

                    writer.Write(") IS ");
                    writer.Write(newFunction.Comment);
                    writer.WriteLine(';');
                }
                else if (oldFun.Comment != null && newFunction.Comment == null)
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("COMMENT ON FUNCTION ");
                    writer.Write(PgDiffUtils.GetQuotedName(newFunction.Name));
                    writer.Write('(');

                    var addComma = false;

                    foreach (var argument in newFunction.GetArguments())
                    {
                        if (addComma)
                        {
                            writer.Write(", ");
                        }
                        else
                        {
                            addComma = true;
                        }

                        writer.Write(argument.GetDeclaration(false));
                    }

                    writer.WriteLine(") IS NULL;");
                }
            }
        }
Exemple #16
0
        public static void AlterViews(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
        {
            if (oldSchema == null)
            {
                return;
            }

            foreach (var oldView in oldSchema.GetViews())
            {
                var newView = newSchema.GetView(oldView.Name);
                if (newView == null)
                {
                    continue;
                }

                DiffDefaultValues(writer, oldView, newView, searchPathHelper);

                if (oldView.Comment == null &&
                    newView.Comment != null ||
                    oldView.Comment != null &&
                    newView.Comment != null &&
                    !oldView.Comment.Equals(
                        newView.Comment))
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("COMMENT ON VIEW ");
                    writer.Write(PgDiffUtils.GetQuotedName(newView.Name));
                    writer.Write(" IS ");
                    writer.Write(newView.Comment);
                    writer.WriteLine(';');
                }
                else if (oldView.Comment != null && newView.Comment == null)
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("COMMENT ON VIEW ");
                    writer.Write(PgDiffUtils.GetQuotedName(newView.Name));
                    writer.WriteLine(" IS NULL;");
                }

                var columnNames = newView.ColumnComments.Select(c => c.ColumnName).ToList();

                foreach (var columnComment in oldView.ColumnComments)
                {
                    if (!columnNames.Contains(columnComment.ColumnName))
                    {
                        columnNames.Add(columnComment.ColumnName);
                    }
                }

                foreach (var columnName in columnNames)
                {
                    var oldColumnComment = oldView.ColumnComments.FirstOrDefault(cc => columnName.Equals(cc.ColumnName));
                    var newColumnComment = newView.ColumnComments.FirstOrDefault(cc => columnName.Equals(cc.ColumnName));

                    if (oldColumnComment == null && newColumnComment != null ||
                        oldColumnComment != null && newColumnComment != null &&
                        !oldColumnComment.Comment.Equals(
                            newColumnComment.Comment))
                    {
                        searchPathHelper.OutputSearchPath(writer);
                        writer.WriteLine();
                        writer.Write("COMMENT ON COLUMN ");
                        writer.Write(PgDiffUtils.GetQuotedName(newView.Name));
                        writer.Write('.');
                        writer.Write(PgDiffUtils.GetQuotedName(newColumnComment.ColumnName));
                        writer.Write(" IS ");
                        writer.Write(newColumnComment.Comment);
                        writer.WriteLine(';');
                    }
                    else if (oldColumnComment != null &&
                             newColumnComment == null)
                    {
                        searchPathHelper.OutputSearchPath(writer);
                        writer.WriteLine();
                        writer.Write("COMMENT ON COLUMN ");
                        writer.Write(PgDiffUtils.GetQuotedName(newView.Name));
                        writer.Write('.');
                        writer.Write(PgDiffUtils.GetQuotedName(oldColumnComment.ColumnName));
                        writer.WriteLine(" IS NULL;");
                    }
                }
            }
        }
Exemple #17
0
        private static void DiffDefaultValues(TextWriter writer, PgView oldView, PgView newView, SearchPathHelper searchPathHelper)
        {
            var oldValues = oldView.DefaultValues;
            var newValues = newView.DefaultValues;

            // modify defaults that are in old view
            foreach (var oldValue in oldValues)
            {
                var found = false;

                foreach (var newValue in newValues)
                {
                    if (oldValue.ColumnName.Equals(newValue.ColumnName))
                    {
                        found = true;

                        if (!oldValue._DefaultValue.Equals(newValue._DefaultValue))
                        {
                            searchPathHelper.OutputSearchPath(writer);
                            writer.WriteLine();
                            writer.Write("ALTER TABLE ");
                            writer.Write(PgDiffUtils.GetQuotedName(newView.Name));
                            writer.Write(" ALTER COLUMN ");
                            writer.Write(PgDiffUtils.GetQuotedName(newValue.ColumnName));
                            writer.Write(" SET DEFAULT ");
                            writer.Write(newValue._DefaultValue);
                            writer.WriteLine(';');
                        }

                        break;
                    }
                }

                if (!found)
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("ALTER TABLE ");
                    writer.Write(PgDiffUtils.GetQuotedName(newView.Name));
                    writer.Write(" ALTER COLUMN ");
                    writer.Write(PgDiffUtils.GetQuotedName(oldValue.ColumnName));
                    writer.WriteLine(" DROP DEFAULT;");
                }
            }

            // add new defaults
            foreach (var newValue in newValues)
            {
                var found = oldValues.Any(ov => newValue.ColumnName.Equals(ov.ColumnName));

                if (found)
                {
                    continue;
                }

                searchPathHelper.OutputSearchPath(writer);
                writer.WriteLine();
                writer.Write("ALTER TABLE ");
                writer.Write(PgDiffUtils.GetQuotedName(newView.Name));
                writer.Write(" ALTER COLUMN ");
                writer.Write(PgDiffUtils.GetQuotedName(newValue.ColumnName));
                writer.Write(" SET DEFAULT ");
                writer.Write(newValue._DefaultValue);
                writer.WriteLine(';');
            }
        }
Exemple #18
0
        public static void AlterSequences(TextWriter writer, PgDiffArguments arguments,
                                          PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
        {
            if (oldSchema == null)
            {
                return;
            }

            var sbSql = new StringBuilder(100);

            foreach (var newSequence in newSchema.GetSequences())
            {
                var oldSequence = oldSchema.GetSequence(newSequence.Name);

                if (oldSequence == null)
                {
                    continue;
                }

                sbSql.Length = 0;

                var oldIncrement = oldSequence.Increment;
                var newIncrement = newSequence.Increment;

                if (newIncrement != null && !newIncrement.Equals(oldIncrement))
                {
                    sbSql.Append("\n\tINCREMENT BY ");
                    sbSql.Append(newIncrement);
                }

                var oldMinValue = oldSequence.MinValue;
                var newMinValue = newSequence.MinValue;

                if (newMinValue == null && oldMinValue != null)
                {
                    sbSql.Append("\n\tNO MINVALUE");
                }
                else if (newMinValue != null && !newMinValue.Equals(oldMinValue))
                {
                    sbSql.Append("\n\tMINVALUE ");
                    sbSql.Append(newMinValue);
                }

                var oldMaxValue = oldSequence.MaxValue;
                var newMaxValue = newSequence.MaxValue;

                if (newMaxValue == null && oldMaxValue != null)
                {
                    sbSql.Append("\n\tNO MAXVALUE");
                }
                else if (newMaxValue != null &&
                         !newMaxValue.Equals(oldMaxValue))
                {
                    sbSql.Append("\n\tMAXVALUE ");
                    sbSql.Append(newMaxValue);
                }

                if (!arguments.IgnoreStartWith)
                {
                    var oldStart = oldSequence.StartWith;
                    var newStart = newSequence.StartWith;

                    if (newStart != null && !newStart.Equals(oldStart))
                    {
                        sbSql.Append("\n\tRESTART WITH ");
                        sbSql.Append(newStart);
                    }
                }

                var oldCache = oldSequence.Cache;
                var newCache = newSequence.Cache;

                if (newCache != null && !newCache.Equals(oldCache))
                {
                    sbSql.Append("\n\tCACHE ");
                    sbSql.Append(newCache);
                }

                var oldCycle = oldSequence.Cycle;
                var newCycle = newSequence.Cycle;

                if (oldCycle && !newCycle)
                {
                    sbSql.Append("\n\tNO CYCLE");
                }
                else if (!oldCycle && newCycle)
                {
                    sbSql.Append("\n\tCYCLE");
                }

                var oldOwnedBy = oldSequence.OwnedBy;
                var newOwnedBy = newSequence.OwnedBy;

                if (newOwnedBy != null && !newOwnedBy.Equals(oldOwnedBy))
                {
                    sbSql.Append("\n\tOWNED BY ");
                    sbSql.Append(newOwnedBy);
                }

                if (sbSql.Length > 0)
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("ALTER SEQUENCE "
                                 + PgDiffUtils.GetQuotedName(newSequence.Name));
                    writer.Write(sbSql.ToString());
                    writer.WriteLine(';');
                }

                if (oldSequence.Comment == null &&
                    newSequence.Comment != null ||
                    oldSequence.Comment != null &&
                    newSequence.Comment != null &&
                    !oldSequence.Comment.Equals(
                        newSequence.Comment))
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("COMMENT ON SEQUENCE ");
                    writer.Write(PgDiffUtils.GetQuotedName(newSequence.Name));
                    writer.Write(" IS ");
                    writer.Write(newSequence.Comment);
                    writer.WriteLine(';');
                }
                else if (oldSequence.Comment != null && newSequence.Comment == null)
                {
                    searchPathHelper.OutputSearchPath(writer);
                    writer.WriteLine();
                    writer.Write("COMMENT ON SEQUENCE ");
                    writer.Write(newSequence.Name);
                    writer.WriteLine(" IS NULL;");
                }
            }
        }
Exemple #19
0
        private static void UpdateSchemas(TextWriter writer, PgDiffArguments arguments, PgDatabase oldDatabase, PgDatabase newDatabase)
        {
            var setSearchPath = newDatabase.Schemas.Count > 1 ||
                                !newDatabase.Schemas[0].Name.Equals("public");

            foreach (var newSchema in newDatabase.Schemas)
            {
                var searchPathHelper = setSearchPath
                    ? new SearchPathHelper($"SET search_path = {PgDiffUtils.GetQuotedName(newSchema.Name, true)}, pg_catalog;")
                    : new SearchPathHelper(null);

                var oldSchema = oldDatabase.GetSchema(newSchema.Name);

                if (oldSchema != null)
                {
                    if (oldSchema.Comment == null &&
                        newSchema.Comment != null ||
                        oldSchema.Comment != null &&
                        newSchema.Comment != null &&
                        !oldSchema.Comment.Equals(
                            newSchema.Comment))
                    {
                        writer.WriteLine();
                        writer.Write("COMMENT ON SCHEMA ");
                        writer.Write(PgDiffUtils.GetQuotedName(newSchema.Name));
                        writer.Write(" IS ");
                        writer.Write(newSchema.Comment);
                        writer.WriteLine(';');
                    }
                    else if (oldSchema.Comment != null && newSchema.Comment == null)
                    {
                        writer.WriteLine();
                        writer.Write("COMMENT ON SCHEMA ");
                        writer.Write(PgDiffUtils.GetQuotedName(newSchema.Name));
                        writer.WriteLine(" IS NULL;");
                    }
                }

                PgDiffTriggers.DropTriggers(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffFunctions.DropFunctions(writer, arguments, oldSchema, newSchema, searchPathHelper);
                PgDiffViews.DropViews(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffConstraints.DropConstraints(writer, oldSchema, newSchema, true, searchPathHelper);
                PgDiffConstraints.DropConstraints(writer, oldSchema, newSchema, false, searchPathHelper);
                PgDiffIndexes.DropIndexes(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffTables.DropClusters(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffTables.DropTables(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffSequences.DropSequences(writer, oldSchema, newSchema, searchPathHelper);

                PgDiffSequences.CreateSequences(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffSequences.AlterSequences(writer, arguments, oldSchema, newSchema, searchPathHelper);
                PgDiffTables.CreateTables(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffTables.AlterTables(writer, arguments, oldSchema, newSchema, searchPathHelper);
                PgDiffSequences.AlterCreatedSequences(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffFunctions.CreateFunctions(writer, arguments, oldSchema, newSchema, searchPathHelper);
                PgDiffConstraints.CreateConstraints(writer, oldSchema, newSchema, true, searchPathHelper);
                PgDiffConstraints.CreateConstraints(writer, oldSchema, newSchema, false, searchPathHelper);
                PgDiffIndexes.CreateIndexes(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffTables.CreateClusters(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffTriggers.CreateTriggers(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffViews.CreateViews(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffViews.AlterViews(writer, oldSchema, newSchema, searchPathHelper);

                PgDiffFunctions.AlterComments(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffConstraints.AlterComments(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffIndexes.AlterComments(writer, oldSchema, newSchema, searchPathHelper);
                PgDiffTriggers.AlterComments(writer, oldSchema, newSchema, searchPathHelper);
            }
        }
Exemple #20
0
        public static void AlterComments(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
        {
            if (oldSchema == null)
            {
                return;
            }

            foreach (var oldTable in oldSchema.GetTables())
            {
                var newTable = newSchema.GetTable(oldTable.Name);

                if (newTable == null)
                {
                    continue;
                }

                foreach (var oldConstraint in oldTable.Constraints)
                {
                    var newConstraint =
                        newTable.GetConstraint(oldConstraint.Name);

                    if (newConstraint == null)
                    {
                        continue;
                    }

                    if (oldConstraint.Comment == null &&
                        newConstraint.Comment != null ||
                        oldConstraint.Comment != null &&
                        newConstraint.Comment != null &&
                        !oldConstraint.Comment.Equals(
                            newConstraint.Comment))
                    {
                        searchPathHelper.OutputSearchPath(writer);
                        writer.WriteLine();
                        writer.Write("COMMENT ON ");

                        if (newConstraint.IsPrimaryKeyConstraint())
                        {
                            writer.Write("INDEX ");
                            writer.Write(PgDiffUtils.GetQuotedName(newConstraint.Name));
                        }
                        else
                        {
                            writer.Write("CONSTRAINT ");
                            writer.Write(PgDiffUtils.GetQuotedName(newConstraint.Name));
                            writer.Write(" ON ");
                            writer.Write(PgDiffUtils.GetQuotedName(newConstraint.TableName));
                        }

                        writer.Write(" IS ");
                        writer.Write(newConstraint.Comment);
                        writer.WriteLine(';');
                    }
                    else if (oldConstraint.Comment != null && newConstraint.Comment == null)
                    {
                        searchPathHelper.OutputSearchPath(writer);
                        writer.WriteLine();
                        writer.Write("COMMENT ON ");

                        if (newConstraint.IsPrimaryKeyConstraint())
                        {
                            writer.Write("INDEX ");
                            writer.Write(PgDiffUtils.GetQuotedName(newConstraint.Name));
                        }
                        else
                        {
                            writer.Write("CONSTRAINT ");
                            writer.Write(PgDiffUtils.GetQuotedName(newConstraint.Name));
                            writer.Write(" ON ");
                            writer.Write(PgDiffUtils.GetQuotedName(newConstraint.TableName));
                        }

                        writer.WriteLine(" IS NULL;");
                    }
                }
            }
        }