/// <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"); } }