/// <summary>
/// Outputs statements for creation of clusters.
/// </summary>
public static void DropClusters(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (PgTable newTable in newSchema.Tables)
{
PgTable oldTable;
if (oldSchema == null)
{
oldTable = null;
}
else
{
oldTable = oldSchema.GetTable(newTable.Name);
}
string oldCluster;
if (oldTable == null)
{
oldCluster = null;
}
else
{
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(PgDiffStringExtension.QuoteName(newTable.Name));
writer.WriteLine(" SET WITHOUT CLUSTER;");
}
}
}
/// <summary>
/// Outputs statements for addition, removal and modifications of table
/// columns.
/// </summary>
private static void UpdateTableColumns(StreamWriter writer, PgTable oldTable, PgTable newTable, SearchPathHelper searchPathHelper)
{
IList <string> statements = new List <string>();
IList <PgColumn> dropDefaultsColumns = new List <PgColumn>();
AddDropTableColumns(statements, oldTable, newTable);
AddCreateTableColumns(statements, oldTable, newTable, dropDefaultsColumns, false);
AddModifyTableColumns(statements, oldTable, newTable, dropDefaultsColumns, false);
if (statements.Count > 0)
{
var quotedTableName = PgDiffStringExtension.QuoteName(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(PgDiffStringExtension.QuoteName(dropDefaultsColumns[i].Name));
writer.Write(" DROP DEFAULT");
writer.WriteLine((i + 1) < dropDefaultsColumns.Count ? "," : ";");
}
}
}
}
/// <summary>
/// Outputs statements for new or modified functions.
/// </summary>
public static void Create(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper, bool ignoreFunctionWhitespace)
{
// Add new functions and replace modified functions
foreach (PgFunction newFunction in newSchema.Functions)
{
PgFunction oldFunction;
if (oldSchema == null)
{
oldFunction = null;
}
else
{
oldFunction = oldSchema.GetFunction(newFunction.Signature);
}
if ((oldFunction == null) || !newFunction.Equals(oldFunction, ignoreFunctionWhitespace))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(newFunction.CreationSQL);
}
}
}
/// <summary>
/// Outputs statements for dropping triggers.
/// </summary>
public static void Drop(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (PgTable newTable in newSchema.Tables)
{
PgTable oldTable;
if (oldSchema == null)
{
oldTable = null;
}
else
{
oldTable = oldSchema.GetTable(newTable.Name);
}
// Drop triggers that no more exist or are modified
foreach (PgTrigger trigger in GetDropTriggers(oldTable, newTable))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(trigger.DropSQL);
}
}
}
/// <summary>
/// Outputs statements for creation of new triggers.
/// </summary>
public static void Create(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (PgTable newTable in newSchema.Tables)
{
PgTable oldTable;
if (oldSchema == null)
{
oldTable = null;
}
else
{
oldTable = oldSchema.GetTable(newTable.Name);
}
// Add new triggers
foreach (PgTrigger trigger in GetNewTriggers(oldTable, newTable))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(trigger.CreationSQL);
}
}
}
/// <summary>
/// Outputs statements for new or modified aggregates.
/// </summary>
/// <param name="writer">Writer the output should be written to.</param>
/// <param name="oldSchema">Original schema.</param>
/// <param name="newSchema">New schema.</param>
/// <param name="searchPathHelper">Search path helper.</param>
public static void Create(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
// Add new aggregates and replace modified aggregates
foreach (PgAggregate newAggregate in newSchema.Aggregates)
{
PgAggregate oldAggregate;
if (oldSchema == null)
{
oldAggregate = null;
}
else
{
oldAggregate = oldSchema.GetAggregate(newAggregate.Signature);
}
if ((oldAggregate == null) || !newAggregate.Equals(oldAggregate))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(newAggregate.CreationSQL);
}
}
}
/// <summary>
/// Drops the types.
/// </summary>
internal static void Drop(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
if (oldSchema == null)
{
return;
}
foreach (PgType oldType in oldSchema.Types)
{
if (!newSchema.ContainsType(oldType.Signature))
{
List <string> newValues = newSchema.TypeEntriesChanged(oldType);
if (newValues != null)
{
AddDefinition(writer, oldType, newValues, searchPathHelper);
}
else
{
var newType = newSchema.GetEnum(oldType.Name);
if (newType != null)
{
// definitions of type changed
// to avoid conflicts, dont delete schema and ignore the additional definition
writer.WriteLine("-- Type was not dropped because of conflicts");
}
else
{
// type was deleted
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(oldType.DropSQL);
}
}
}
}
}
/// <summary>
/// Creates the types.
/// </summary>
internal static void Create(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (PgType newType in newSchema.Types)
{
if (oldSchema == null || !oldSchema.ContainsType(newType.Signature))
{
PgType oldType = null;
if (oldSchema != null)
{
oldType = oldSchema.GetEnum(newType.Name);
}
if (oldType != null)
{
// if definitions of type changed it will be ignored to
continue;
}
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(newType.CreationSQL);
}
}
}
/// <summary>
/// Outputs statement for modified sequences.
/// </summary>
public static void Alter(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper, bool ignoreStartWith)
{
if (oldSchema == null)
{
return;
}
var alterSequenceSql = new StringBuilder(100);
foreach (PgSequence newSequence in newSchema.Sequences)
{
PgSequence oldSequence = oldSchema.GetSequence(newSequence.Name);
if (oldSequence == null)
{
continue;
}
alterSequenceSql.Length = 0;
var oldIncrement = oldSequence.Increment;
var newIncrement = newSequence.Increment;
if (newIncrement != null && !newIncrement.Equals(oldIncrement))
{
alterSequenceSql.Append("\n\tINCREMENT BY ");
alterSequenceSql.Append(newIncrement);
}
var oldMinValue = oldSequence.MinValue;
var newMinValue = newSequence.MinValue;
if (newMinValue == null && oldMinValue != null)
{
alterSequenceSql.Append("\n\tNO MINVALUE");
}
else if (newMinValue != null && !newMinValue.Equals(oldMinValue))
{
alterSequenceSql.Append("\n\tMINVALUE ");
alterSequenceSql.Append(newMinValue);
}
var oldMaxValue = oldSequence.MaxValue;
var newMaxValue = newSequence.MaxValue;
if (newMaxValue == null && oldMaxValue != null)
{
alterSequenceSql.Append("\n\tNO MAXVALUE");
}
else if (newMaxValue != null && !newMaxValue.Equals(oldMaxValue))
{
alterSequenceSql.Append("\n\tMAXVALUE ");
alterSequenceSql.Append(newMaxValue);
}
if (!ignoreStartWith)
{
var oldStart = oldSequence.StartWith;
var newStart = newSequence.StartWith;
if (newStart != null && !newStart.Equals(oldStart))
{
alterSequenceSql.Append("\n\tRESTART WITH ");
alterSequenceSql.Append(newStart);
}
}
var oldCache = oldSequence.Cache;
var newCache = newSequence.Cache;
if (newCache != null && !newCache.Equals(oldCache))
{
alterSequenceSql.Append("\n\tCACHE ");
alterSequenceSql.Append(newCache);
}
var oldCycle = oldSequence.Cycle;
var newCycle = newSequence.Cycle;
if (oldCycle && !newCycle)
{
alterSequenceSql.Append("\n\tNO CYCLE");
}
else if (!oldCycle && newCycle)
{
alterSequenceSql.Append("\n\tCYCLE");
}
var oldOwnedBy = oldSequence.Owner;
var newOwnedBy = newSequence.Owner;
if (newOwnedBy != null && !newOwnedBy.Equals(oldOwnedBy))
{
alterSequenceSql.Append("\n\tOWNED BY ");
alterSequenceSql.Append(newOwnedBy);
}
if (alterSequenceSql.Length > 0)
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("ALTER SEQUENCE " + PgDiffStringExtension.QuoteName(newSequence.Name));
writer.Write(alterSequenceSql.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(PgDiffStringExtension.QuoteName(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;");
}
}
}
/// <summary>
/// Outputs statements for function comments that have changed.
/// </summary>
public static void AlterComments(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
if (oldSchema == null)
{
return;
}
foreach (PgFunction oldfunction in oldSchema.Functions)
{
PgFunction newFunction = newSchema.GetFunction(oldfunction.Signature);
if (newFunction == null)
{
continue;
}
if ((oldfunction.Comment == null && newFunction.Comment != null) ||
(oldfunction.Comment != null && newFunction.Comment != null && !oldfunction.Comment.Equals(newFunction.Comment)))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("COMMENT ON FUNCTION ");
writer.Write(PgDiffStringExtension.QuoteName(newFunction.Name));
writer.Write('(');
var addComma = false;
foreach (PgFunction.Argument argument in newFunction.Arguments)
{
if (addComma)
{
writer.Write(", ");
}
else
{
addComma = true;
}
writer.Write(argument.GetDeclaration(false));
}
writer.Write(") IS ");
writer.Write(newFunction.Comment);
writer.WriteLine(';');
}
else if (oldfunction.Comment != null && newFunction.Comment == null)
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("COMMENT ON FUNCTION ");
writer.Write(PgDiffStringExtension.QuoteName(newFunction.Name));
writer.Write('(');
var addComma = false;
foreach (PgFunction.Argument argument in newFunction.Arguments)
{
if (addComma)
{
writer.Write(", ");
}
else
{
addComma = true;
}
writer.Write(argument.GetDeclaration(false));
}
writer.WriteLine(") IS NULL;");
}
}
}
/// <summary>
/// Outputs statements for constraint comments that have changed.
/// </summary>
public static void AlterComments(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
if (oldSchema == null)
{
return;
}
foreach (PgTable oldTable in oldSchema.Tables)
{
PgTable newTable = newSchema.GetTable(oldTable.Name);
if (newTable == null)
{
continue;
}
foreach (PgConstraint oldConstraint in oldTable.Constraints)
{
PgConstraint 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.PrimaryKeyConstraint)
{
writer.Write("INDEX ");
writer.Write(PgDiffStringExtension.QuoteName(newConstraint.Name));
}
else
{
writer.Write("CONSTRAINT ");
writer.Write(PgDiffStringExtension.QuoteName(newConstraint.Name));
writer.Write(" ON ");
writer.Write(PgDiffStringExtension.QuoteName(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.PrimaryKeyConstraint)
{
writer.Write("INDEX ");
writer.Write(PgDiffStringExtension.QuoteName(newConstraint.Name));
}
else
{
writer.Write("CONSTRAINT ");
writer.Write(PgDiffStringExtension.QuoteName(newConstraint.Name));
writer.Write(" ON ");
writer.Write(PgDiffStringExtension.QuoteName(newConstraint.TableName));
}
writer.WriteLine(" IS NULL;");
}
}
}
}
/// <summary>
/// Outputs statements for aggregate comments that have changed.
/// </summary>
public static void AlterComments(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
if (oldSchema == null)
{
return;
}
foreach (PgAggregate oldAggregate in oldSchema.Aggregates)
{
PgAggregate newAggregate = newSchema.GetAggregate(oldAggregate.Signature);
if (newAggregate == null)
{
continue;
}
if ((oldAggregate.Comment == null && newAggregate.Comment != null) ||
(oldAggregate.Comment != null && newAggregate.Comment != null && !oldAggregate.Comment.Equals(newAggregate.Comment)))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("COMMENT ON AGGREGATE ");
writer.Write(PgDiffStringExtension.QuoteName(newAggregate.Name));
writer.Write('(');
var addComma = false;
foreach (PgAggregate.Argument argument in newAggregate.Arguments)
{
if (addComma)
{
writer.Write(", ");
}
else
{
addComma = true;
}
writer.Write(argument.DataType);
}
writer.Write(") IS ");
writer.Write(newAggregate.Comment);
writer.WriteLine(';');
}
else if (oldAggregate.Comment != null && newAggregate.Comment == null)
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("COMMENT ON AGGREGATE ");
writer.Write(PgDiffStringExtension.QuoteName(newAggregate.Name));
writer.Write('(');
var addComma = false;
foreach (PgAggregate.Argument argument in newAggregate.Arguments)
{
if (addComma)
{
writer.Write(", ");
}
else
{
addComma = true;
}
writer.Write(argument.DataType);
}
writer.WriteLine(") IS NULL;");
}
}
}
/// <summary>
/// Outputs statements for altering view default values.
/// </summary>
public static void Alter(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
if (oldSchema == null)
{
return;
}
foreach (PgView oldView in oldSchema.Views)
{
PgView 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(PgDiffStringExtension.QuoteName(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(PgDiffStringExtension.QuoteName(newView.Name));
writer.WriteLine(" IS NULL;");
}
IList <string> columnNames = new List <string>(newView.ColumnComments.Count);
foreach (PgView.ColumnComment columnComment in newView.ColumnComments)
{
columnNames.Add(columnComment.ColumnName);
}
foreach (PgView.ColumnComment columnComment in oldView.ColumnComments)
{
if (!columnNames.Contains(columnComment.ColumnName))
{
columnNames.Add(columnComment.ColumnName);
}
}
foreach (var columnName in columnNames)
{
PgView.ColumnComment oldColumnComment = null;
PgView.ColumnComment newColumnComment = null;
foreach (PgView.ColumnComment columnComment in oldView.ColumnComments)
{
if (columnName.Equals(columnComment.ColumnName))
{
oldColumnComment = columnComment;
break;
}
}
foreach (PgView.ColumnComment columnComment in newView.ColumnComments)
{
if (columnName.Equals(columnComment.ColumnName))
{
newColumnComment = columnComment;
break;
}
}
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(PgDiffStringExtension.QuoteName(newView.Name));
writer.Write('.');
writer.Write(PgDiffStringExtension.QuoteName(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(PgDiffStringExtension.QuoteName(newView.Name));
writer.Write('.');
writer.Write(PgDiffStringExtension.QuoteName(oldColumnComment.ColumnName));
writer.WriteLine(" IS NULL;");
}
}
}
}
/// <summary>
/// Diffs default values in views.
/// </summary>
private static void DiffDefaultValues(StreamWriter writer, PgView oldView, PgView newView, SearchPathHelper searchPathHelper)
{
IList <PgView.DefaultValue> oldValues = oldView.DefaultValues;
IList <PgView.DefaultValue> newValues = newView.DefaultValues;
// modify defaults that are in old view
foreach (PgView.DefaultValue oldValue in oldValues)
{
var found = false;
foreach (PgView.DefaultValue newValue in newValues)
{
if (oldValue.ColumnName.Equals(newValue.ColumnName))
{
found = true;
if (!oldValue.Value.Equals(newValue.Value))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("ALTER TABLE ");
writer.Write(PgDiffStringExtension.QuoteName(newView.Name));
writer.Write(" ALTER COLUMN ");
writer.Write(PgDiffStringExtension.QuoteName(newValue.ColumnName));
writer.Write(" SET DEFAULT ");
writer.Write(newValue.Value);
writer.WriteLine(';');
}
break;
}
}
if (!found)
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("ALTER TABLE ");
writer.Write(PgDiffStringExtension.QuoteName(newView.Name));
writer.Write(" ALTER COLUMN ");
writer.Write(PgDiffStringExtension.QuoteName(oldValue.ColumnName));
writer.WriteLine(" DROP DEFAULT;");
}
}
// add new defaults
foreach (PgView.DefaultValue newValue in newValues)
{
var found = false;
foreach (PgView.DefaultValue oldValue in oldValues)
{
if (newValue.ColumnName.Equals(oldValue.ColumnName))
{
found = true;
break;
}
}
if (found)
{
continue;
}
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("ALTER TABLE ");
writer.Write(PgDiffStringExtension.QuoteName(newView.Name));
writer.Write(" ALTER COLUMN ");
writer.Write(PgDiffStringExtension.QuoteName(newValue.ColumnName));
writer.Write(" SET DEFAULT ");
writer.Write(newValue.Value);
writer.WriteLine(';');
}
}