/// <summary>
/// Generate the needed alter table xxx set storage when needed.
/// </summary>
private static void AddAlterStorage(StreamWriter writer, PgTable oldTable, PgTable newTable, SearchPathHelper searchPathHelper)
{
foreach (PgColumn newColumn in newTable.Columns)
{
PgColumn oldColumn = oldTable.GetColumn(newColumn.Name);
var oldStorage = (oldColumn == null || oldColumn.Storage == null || oldColumn.Storage == string.Empty) ? null : oldColumn.Storage;
var newStorage = (newColumn.Storage == null || newColumn.Storage == string.Empty) ? null : newColumn.Storage;
if (newStorage == null && oldStorage != null)
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine($"WarningUnableToDetermineStorageType {newTable.Name + '.' + newColumn.Name}");
continue;
}
if (newStorage == null || newStorage.Equals(oldStorage, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("ALTER TABLE ONLY ");
writer.Write(PgDiffStringExtension.QuoteName(newTable.Name));
writer.Write(" ALTER COLUMN ");
writer.Write(PgDiffStringExtension.QuoteName(newColumn.Name));
writer.Write(" SET STORAGE ");
writer.Write(newStorage);
writer.Write(';');
}
}
private static void ParseColumn(Parser parser, PgTable table)
{
var column = new PgColumn(ParserUtils.GetObjectName(parser.ParseIdentifier()));
table.AddColumn(column);
column.ParseDefinition(parser.GetExpression());
}
/// <summary>
/// Adds statements for modification of columns to the list of statements.
/// </summary>
private static void AddModifyTableColumns(IList <string> statements, PgTable oldTable, PgTable newTable, IList <PgColumn> dropDefaultsColumns, bool addDefaults)
{
foreach (PgColumn newColumn in newTable.Columns)
{
if (!oldTable.ContainsColumn(newColumn.Name))
{
continue;
}
PgColumn oldColumn = oldTable.GetColumn(newColumn.Name);
var newColumnName = PgDiffStringExtension.QuoteName(newColumn.Name);
if (!oldColumn.Type.Equals(newColumn.Type))
{
statements.Add($"\tALTER COLUMN {newColumnName} TYPE {newColumn.Type} /* TypeParameterChange of table {newTable.Name}. Old: {oldColumn.Type} New: {newColumn.Type} */");
}
var oldDefault = oldColumn.DefaultValue ?? string.Empty;
var newDefault = newColumn.DefaultValue ?? string.Empty;
if (!oldDefault.Equals(newDefault))
{
if (newDefault.Length == 0)
{
statements.Add("\tALTER COLUMN " + newColumnName + " DROP DEFAULT");
}
else
{
statements.Add("\tALTER COLUMN " + newColumnName + " SET DEFAULT " + newDefault);
}
}
if (oldColumn.NullValue != newColumn.NullValue)
{
if (newColumn.NullValue)
{
statements.Add("\tALTER COLUMN " + newColumnName + " DROP NOT NULL");
}
else
{
if (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");
}
}
}
}
/// <summary>
/// Outputs statements for tables and columns for which comments have
/// changed.
/// </summary>
private static void AlterComments(StreamWriter 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(PgDiffStringExtension.QuoteName(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(PgDiffStringExtension.QuoteName(newTable.Name));
writer.WriteLine(" IS NULL;");
}
foreach (PgColumn newColumn in newTable.Columns)
{
PgColumn oldColumn = oldTable.GetColumn(newColumn.Name);
var oldComment = oldColumn?.Comment;
var newComment = newColumn.Comment;
if (newComment != null && (oldComment == null ? newComment != null : !oldComment.Equals(newComment)))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("COMMENT ON COLUMN ");
writer.Write(PgDiffStringExtension.QuoteName(newTable.Name));
writer.Write('.');
writer.Write(PgDiffStringExtension.QuoteName(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(PgDiffStringExtension.QuoteName(newTable.Name));
writer.Write('.');
writer.Write(PgDiffStringExtension.QuoteName(newColumn.Name));
writer.WriteLine(" IS NULL;");
}
}
}
internal static Collection<PgColumn> GetColumns(PgTable pgTable)
{
Collection<PgColumn> columns = new Collection<PgColumn>();
#region sql
string sql = FileHelper.ReadSqlResource("columns.sql");
#endregion
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@SchemaName", pgTable.SchemaName);
command.Parameters.AddWithValue("@TableName", pgTable.Name);
using (DataTable table = DbOperation.GetDataTable(command))
{
if (table.Rows.Count.Equals(0))
{
return columns;
}
foreach (DataRow row in table.Rows)
{
PgColumn column = new PgColumn
{
SchemaName = pgTable.SchemaName,
TableName = pgTable.Name,
Name = Conversion.TryCastString(row["column_name"]),
OrdinalPosition = Conversion.TryCastInteger(row["ordinal_position"]),
DefaultValue = Conversion.TryCastString(row["column_default"]),
DataType = Conversion.TryCastString(row["data_type"]),
IsNullable = Conversion.TryCastBoolean(row["is_nullable"]),
MaxLength = Conversion.TryCastInteger(row["character_maximum_length"]),
Description = Conversion.TryCastString(row["description"]),
PrimaryKeyConstraintName = Conversion.TryCastString(row["key"]),
ForiegnKeyName = Conversion.TryCastString(row["constraint_name"]),
ForeignSchemaName = Conversion.TryCastString(row["references_schema"]),
ForeignTableName = Conversion.TryCastString(row["references_table"]),
ForeignColumnName = Conversion.TryCastString(row["references_field"])
};
column.IsPrimaryKey = !string.IsNullOrWhiteSpace(column.PrimaryKeyConstraintName);
columns.Add(column);
}
}
}
return columns;
}
/// <summary>
/// Parses COMMENT ON COLUMN.
/// </summary>
private static void ParseColumn(Parser parser, PgDatabase database)
{
var columnName = parser.ParseIdentifier();
var objectName = ParserUtils.GetObjectName(columnName);
var tableName = ParserUtils.GetSecondObjectName(columnName);
var schemaName = ParserUtils.GetThirdObjectName(columnName);
if (database.SchemaIsIgnored(schemaName))
{
return;
}
PgSchema schema = database.GetSchema(schemaName);
PgTable table = schema.GetTable(tableName);
if (table == null)
{
PgView view = schema.GetView(tableName);
parser.Expect("IS");
var comment = GetComment(parser);
if (comment == null)
{
view.RemoveColumnComment(objectName);
}
else
{
view.AddColumnComment(objectName, comment);
}
parser.Expect(";");
}
else
{
PgColumn column = table.GetColumn(objectName);
if (column == null)
{
throw new TeamworkParserException($"CannotFindColumnInTable {columnName} from table {table.Name}");
}
parser.Expect("IS");
column.Comment = GetComment(parser);
parser.Expect(";");
}
}
internal static Collection <PgColumn> GetColumns(PgTable pgTable)
{
Collection <PgColumn> columns = new Collection <PgColumn>();
#region sql
string sql = FileHelper.ReadSqlResource("columns.sql");
#endregion
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@SchemaName", pgTable.SchemaName);
command.Parameters.AddWithValue("@TableName", pgTable.Name);
using (DataTable table = DbOperation.GetDataTable(command))
{
if (table.Rows.Count.Equals(0))
{
return(columns);
}
foreach (DataRow row in table.Rows)
{
PgColumn column = new PgColumn
{
SchemaName = pgTable.SchemaName,
TableName = pgTable.Name,
Name = Conversion.TryCastString(row["column_name"]),
OrdinalPosition = Conversion.TryCastInteger(row["ordinal_position"]),
DefaultValue = Conversion.TryCastString(row["column_default"]),
DataType = Conversion.TryCastString(row["data_type"]),
IsNullable = Conversion.TryCastBoolean(row["is_nullable"]),
MaxLength = Conversion.TryCastInteger(row["character_maximum_length"]),
Description = Conversion.TryCastString(row["description"]),
PrimaryKeyConstraintName = Conversion.TryCastString(row["key"]),
ForiegnKeyName = Conversion.TryCastString(row["constraint_name"]),
ForeignSchemaName = Conversion.TryCastString(row["references_schema"]),
ForeignTableName = Conversion.TryCastString(row["references_table"]),
ForeignColumnName = Conversion.TryCastString(row["references_field"])
};
column.IsPrimaryKey = !string.IsNullOrWhiteSpace(column.PrimaryKeyConstraintName);
columns.Add(column);
}
}
}
return(columns);
}
/// <summary>
/// Generate the needed alter table xxx set statistics when needed.
/// </summary>
private static void AddAlterStatistics(StreamWriter writer, PgTable oldTable, PgTable newTable, SearchPathHelper searchPathHelper)
{
var stats = new Dictionary <string, int?>();
foreach (PgColumn newColumn in newTable.Columns)
{
PgColumn oldColumn = oldTable.GetColumn(newColumn.Name);
if (oldColumn != null)
{
int?oldStat = oldColumn.Statistics;
int?newStat = newColumn.Statistics;
int?newStatValue = null;
if (newStat != null && (oldStat == null || !newStat.Equals(oldStat)))
{
newStatValue = newStat;
}
else if (oldStat != null && newStat == null)
{
newStatValue = Convert.ToInt32(-1);
}
if (newStatValue != null)
{
stats[newColumn.Name] = newStatValue;
}
}
}
foreach (KeyValuePair <string, int?> entry in stats)
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("ALTER TABLE ONLY ");
writer.Write(PgDiffStringExtension.QuoteName(newTable.Name));
writer.Write(" ALTER COLUMN ");
writer.Write(PgDiffStringExtension.QuoteName(entry.Key));
writer.Write(" SET STATISTICS ");
writer.Write(entry.Value);
writer.WriteLine(';');
}
}
/// <summary>
/// Parses ALTER COLUMN action.
/// </summary>
private static void ParseAlterColumn(Parser parser, PgTable table)
{
parser.ExpectOptional("COLUMN");
var columnName = ParserUtils.GetObjectName(parser.ParseIdentifier());
if (parser.ExpectOptional("SET"))
{
if (parser.ExpectOptional("STATISTICS"))
{
PgColumn column = table.GetColumn(columnName);
if (column == null)
{
throw new TeamworkParserException($"CannotFindTableColumn {columnName} in table {table.Name} from {parser.String}");
}
column.Statistics = parser.ParseInteger();
}
else if (parser.ExpectOptional("DEFAULT"))
{
var defaultValue = parser.Expression();
if (table.ContainsColumn(columnName))
{
PgColumn column = table.GetColumn(columnName);
if (column == null)
{
throw new TeamworkParserException($"CannotFindTableColumn {columnName} in table {table.Name} from {parser.String}");
}
column.DefaultValue = defaultValue;
}
else
{
throw new TeamworkParserException($"CannotFindColumnInTable {columnName} in table {table.Name}");
}
}
else if (parser.ExpectOptional("STORAGE"))
{
PgColumn column = table.GetColumn(columnName);
if (column == null)
{
throw new TeamworkParserException($"CannotFindTableColumn {columnName} in table {table.Name} from {parser.String}");
}
if (parser.ExpectOptional("PLAIN"))
{
column.Storage = "PLAIN";
}
else if (parser.ExpectOptional("EXTERNAL"))
{
column.Storage = "EXTERNAL";
}
else if (parser.ExpectOptional("EXTENDED"))
{
column.Storage = "EXTENDED";
}
else if (parser.ExpectOptional("MAIN"))
{
column.Storage = "MAIN";
}
else
{
throw new TeamworkParserException("CannotParseStringUnsupportedCommand");
}
}
else
{
throw new TeamworkParserException("CannotParseStringUnsupportedCommand");
}
}
else
{
throw new TeamworkParserException("CannotParseStringUnsupportedCommand");
}
}
