private static bool IsViewModified(PgView oldView, PgView newView)
{
string[] oldViewColumnNames;
if (oldView.ColumnNames == null || oldView.ColumnNames.Count == 0)
{
oldViewColumnNames = null;
}
else
{
oldViewColumnNames = oldView.ColumnNames.ToArray();
}
string[] newViewColumnNames;
if (newView.ColumnNames == null || newView.ColumnNames.Count == 0)
{
newViewColumnNames = null;
}
else
{
newViewColumnNames = newView.ColumnNames.ToArray();
}
if (oldViewColumnNames == null && newViewColumnNames == null)
{
return(!oldView.Query.Trim().Equals(newView.Query.Trim()));
}
return(!oldViewColumnNames.SequenceEqual(newViewColumnNames));
}
private static void BuildDocumentation(string content, PgView view)
{
content = content.Replace("[DBName]", Program.Database.ToUpperInvariant());
content = Parsers.ViewParser.Parse(content, view);
string targetPath = System.IO.Path.Combine(OutputPath, view.SchemaName, view.Name + ".html");
FileHelper.WriteFile(content, targetPath);
}
internal static void CallAlterTableParserParseView(Parser parser, PgView view, bool outputIgnoredStatements, string viewName, PgDatabase database)
{
var pt = new Microsoft.VisualStudio.TestTools.UnitTesting.PrivateType(typeof(AlterTableParser));
try
{
pt.InvokeStatic("ParseView", new object[] { parser, view, outputIgnoredStatements, viewName, database });
}
catch (System.MissingMethodException missingMethodException)
{
throw new System.NotSupportedException("ParseView with requested parameters is not found. Rerun code generation.", missingMethodException);
}
}
internal static bool CallPgDiffViewsIsViewModified(PgView oldView, PgView newView)
{
var pt = new Microsoft.VisualStudio.TestTools.UnitTesting.PrivateType(typeof(PgDiffViews));
try
{
return((bool)pt.InvokeStatic("IsViewModified", new object[] { oldView, newView }));
}
catch (System.MissingMethodException missingMethodException)
{
throw new System.NotSupportedException("IsViewModified with requested parameters is not found. Rerun code generation.", missingMethodException);
}
}
internal static void CallPgDiffViewsDiffDefaultValues(StreamWriter writer, PgView oldView, PgView newView, SearchPathHelper searchPathHelper)
{
var pt = new Microsoft.VisualStudio.TestTools.UnitTesting.PrivateType(typeof(PgDiffViews));
try
{
pt.InvokeStatic("DiffDefaultValues", new object[] { writer, oldView, newView, searchPathHelper });
}
catch (System.MissingMethodException missingMethodException)
{
throw new System.NotSupportedException("DiffDefaultValues with requested parameters is not found. Rerun code generation.", missingMethodException);
}
}
/// <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(";");
}
}
/// <summary>
/// Parses CREATE VIEW statement.
/// </summary>
public static void Parse(PgDatabase database, string statement)
{
var parser = new Parser(statement);
parser.Expect("CREATE");
parser.ExpectOptional("OR", "REPLACE");
parser.Expect("VIEW");
var viewName = parser.ParseIdentifier();
var columnsExist = parser.ExpectOptional("(");
var columnNames = new List <string>(10);
if (columnsExist)
{
while (!parser.ExpectOptional(")"))
{
columnNames.Add(ParserUtils.GetObjectName(parser.ParseIdentifier()));
parser.ExpectOptional(",");
}
}
parser.ExpectOptional("WITH", "(security_barrier='false')");
parser.Expect("AS");
var query = parser.Rest();
var view = new PgView(ParserUtils.GetObjectName(viewName))
{
ColumnNames = columnNames,
Query = query,
};
var schemaName = ParserUtils.GetSchemaName(viewName, database);
if (database.SchemaIsIgnored(schemaName))
{
return;
}
PgSchema schema = database.GetSchema(schemaName);
if (schema == null)
{
throw new TeamworkParserException($"CannotFindSchema {schemaName} {statement}");
}
schema.Add(view);
}
internal static string Parse(string content, PgView view)
{
StringBuilder items = new StringBuilder();
items.Append(content.Replace("[Name]", view.Name)
.Replace("[ViewSchema]", view.SchemaName)
.Replace("[RowNumber]", view.RowNumber.ToString())
.Replace("[Owner]", view.Owner)
.Replace("[Tablespace]", view.Tablespace)
.Replace("[Definition]", view.Definition)
.Replace("[Description]", view.Description));
content = content.Replace(content, items.ToString());
return(content);
}
/// <summary>
/// Outputs statements for dropping views.
/// </summary>
public static void Drop(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 || IsViewModified(oldView, newView))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(oldView.DropSQL);
}
}
}
/// <summary>
/// Parses COMMENT ON VIEW.
/// </summary>
private static void ParseView(Parser parser, PgDatabase database)
{
var viewName = parser.ParseIdentifier();
var objectName = ParserUtils.GetObjectName(viewName);
var schemaName = ParserUtils.GetSchemaName(viewName, database);
if (database.SchemaIsIgnored(schemaName))
{
return;
}
PgView view = database.GetSchema(schemaName).GetView(objectName);
parser.Expect("IS");
view.Comment = GetComment(parser);
parser.Expect(";");
}
/// <summary>
/// Parses ALTER TABLE view.
/// </summary>
private static void ParseView(Parser parser, PgView view, bool outputIgnoredStatements, string viewName, PgDatabase database)
{
while (!parser.ExpectOptional(";"))
{
if (parser.ExpectOptional("ALTER"))
{
parser.ExpectOptional("COLUMN");
var columnName = ParserUtils.GetObjectName(parser.ParseIdentifier());
if (parser.ExpectOptional("SET", "DEFAULT"))
{
var expression = parser.Expression();
view.AddColumnDefaultValue(columnName, expression);
}
else if (parser.ExpectOptional("DROP", "DEFAULT"))
{
view.RemoveColumnDefaultValue(columnName);
}
else
{
throw new TeamworkParserException("CannotParseStringUnsupportedCommand");
}
}
else if (parser.ExpectOptional("OWNER", "TO"))
{
// we do not parse this one so we just consume the identifier
if (outputIgnoredStatements)
{
database.AddIgnoredStatement("ALTER TABLE " + viewName + " OWNER TO " + parser.ParseIdentifier() + ';');
}
else
{
parser.ParseIdentifier();
}
}
else
{
throw new TeamworkParserException("CannotParseStringUnsupportedCommand");
}
}
}
public static void Parse(PgDatabase database, string statement)
{
var parser = new Parser(statement);
parser.Expect("CREATE");
parser.ExpectOptional("OR", "REPLACE");
parser.Expect("VIEW");
var viewName = parser.ParseIdentifier();
var columnsExist = parser.ExpectOptional("(");
var columnNames = new List <string>();
if (columnsExist)
{
while (!parser.ExpectOptional(")"))
{
columnNames.Add(ParserUtils.GetObjectName(parser.ParseIdentifier()));
parser.ExpectOptional(",");
}
}
parser.Expect("AS");
var query = parser.GetRest();
var view = new PgView(ParserUtils.GetObjectName(viewName));
view.ColumnNames = columnNames;
view.Query = query;
var schemaName = ParserUtils.GetSchemaName(viewName, database);
var schema = database.GetSchema(schemaName);
if (schema == null)
{
throw new Exception(string.Format(Resources.CannotFindSchema, schemaName, statement));
}
schema.AddView(view);
}
/// <summary>
/// Returns true if either column names or query of the view has been
/// modified.
/// </summary>
private static bool IsViewModified(PgView oldView, PgView newView)
{
string[] oldViewColumnNames;
if (oldView.ColumnNames == null || oldView.ColumnNames.Count == 0)
{
oldViewColumnNames = null;
}
else
{
oldViewColumnNames = oldView.ColumnNames.ToArray();
}
string[] newViewColumnNames;
if (newView.ColumnNames == null || newView.ColumnNames.Count == 0)
{
newViewColumnNames = null;
}
else
{
newViewColumnNames = newView.ColumnNames.ToArray();
}
if (oldViewColumnNames == null && newViewColumnNames == null)
{
return(!oldView.Query.Replace(" ", string.Empty).Trim().Equals(newView.Query.Replace(" ", string.Empty).Trim()));
}
else if (oldViewColumnNames == null)
{
return(true);
}
else
{
return(!oldViewColumnNames.Equals(newViewColumnNames));
}
}
internal static Collection <PgView> GetViews(string schemaPattern = ".*", string xSchemaPattern = "")
{
Collection <PgView> views = new Collection <PgView>();
string sql = FileHelper.ReadSqlResource("views-by-schema.sql");
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@SchemaPattern", schemaPattern);
command.Parameters.AddWithValue("@xSchemaPattern", xSchemaPattern);
using (DataTable table = DbOperation.GetDataTable(command))
{
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
PgView view = new PgView
{
RowNumber = Conversion.TryCastLong(row["row_number"]),
Name = Conversion.TryCastString(row["object_name"]),
SchemaName = Conversion.TryCastString(row["object_schema"]),
Tablespace = Conversion.TryCastString(row["tablespace"]),
Owner = Conversion.TryCastString(row["owner"]),
Definition = Conversion.TryCastString(row["definition"]),
Description = Conversion.TryCastString(row["description"])
};
views.Add(view);
}
}
}
}
return(views);
}
/// <summary>
/// Parses ALTER VIEW statement.
/// </summary>
public static void Parse(PgDatabase database, string statement, bool outputIgnoredStatements)
{
var parser = new Parser(statement);
parser.Expect("ALTER", "VIEW");
var viewName = parser.ParseIdentifier();
var schemaName = ParserUtils.GetSchemaName(viewName, database);
if (database.SchemaIsIgnored(schemaName))
{
return;
}
PgSchema schema = database.GetSchema(schemaName);
if (schema == null)
{
throw new TeamworkParserException($"CannotFindSchema {schemaName} from {statement}");
}
var objectName = ParserUtils.GetObjectName(viewName);
PgView view = schema.GetView(objectName);
if (view == null)
{
throw new TeamworkParserException($"CannotFindView {viewName} from {statement}");
}
while (!parser.ExpectOptional(";"))
{
if (parser.ExpectOptional("ALTER"))
{
parser.ExpectOptional("COLUMN");
var columnName = ParserUtils.GetObjectName(parser.ParseIdentifier());
if (parser.ExpectOptional("SET", "DEFAULT"))
{
var expression = parser.Expression();
view.AddColumnDefaultValue(columnName, expression);
}
else if (parser.ExpectOptional("DROP", "DEFAULT"))
{
view.RemoveColumnDefaultValue(columnName);
}
else
{
throw new TeamworkParserException("CannotParseStringUnsupportedCommand");
}
}
else if (parser.ExpectOptional("OWNER", "TO"))
{
// we do not parse this one so we just consume the identifier
if (outputIgnoredStatements)
{
database.AddIgnoredStatement("ALTER TABLE " + viewName + " OWNER TO " + parser.ParseIdentifier() + ';');
}
else
{
parser.ParseIdentifier();
}
}
else
{
throw new TeamworkParserException("CannotParseStringUnsupportedCommand");
}
}
}
/// <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(';');
}
}
/// <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;");
}
}
}
}
internal static void Run(PgView view)
{
string content = FileHelper.ReadResource(TemplatePath);
BuildDocumentation(content, view);
}
/// <summary>
/// Parses ALTER TABLE statement.
/// </summary>
public static void Parse(PgDatabase database, string statement, bool outputIgnoredStatements)
{
var parser = new Parser(statement);
parser.Expect("ALTER", "TABLE");
parser.ExpectOptional("ONLY");
var tableName = parser.ParseIdentifier();
var schemaName = ParserUtils.GetSchemaName(tableName, database);
if (database.SchemaIsIgnored(schemaName))
{
return;
}
PgSchema schema = database.GetSchema(schemaName);
if (schema == null)
{
throw new TeamworkParserException($"CannotFindSchema {schemaName} from {statement}");
}
var objectName = ParserUtils.GetObjectName(tableName);
PgTable table = schema.GetTable(objectName);
if (table == null)
{
PgView view = schema.GetView(objectName);
if (view != null)
{
ParseView(parser, view, outputIgnoredStatements, tableName, database);
return;
}
PgSequence sequence = schema.GetSequence(objectName);
if (sequence != null)
{
ParseSequence(parser, sequence, outputIgnoredStatements, tableName, database);
return;
}
throw new TeamworkParserException($"CannotFindObject in {tableName} from {statement}");
}
while (!parser.ExpectOptional(";"))
{
if (parser.ExpectOptional("ALTER"))
{
ParseAlterColumn(parser, table);
}
else if (parser.ExpectOptional("CLUSTER", "ON"))
{
table.ClusterIndexName = ParserUtils.GetObjectName(parser.ParseIdentifier());
}
else if (parser.ExpectOptional("OWNER", "TO"))
{
// we do not parse this one so we just consume the identifier
if (outputIgnoredStatements)
{
database.AddIgnoredStatement("ALTER TABLE " + tableName + " OWNER TO " + parser.ParseIdentifier() + ';');
}
else
{
parser.ParseIdentifier();
}
}
else if (parser.ExpectOptional("ADD"))
{
if (parser.ExpectOptional("FOREIGN", "KEY"))
{
ParseAddForeignKey(parser, table);
}
else if (parser.ExpectOptional("CONSTRAINT"))
{
ParseAddConstraint(parser, table, schema);
}
else
{
throw new TeamworkParserException("CannotParseStringUnsupportedCommand");
}
}
else if (parser.ExpectOptional("ENABLE"))
{
ParseEnable(parser, outputIgnoredStatements, tableName, database);
}
else if (parser.ExpectOptional("DISABLE"))
{
ParseDisable(parser, outputIgnoredStatements, tableName, database);
}
else if (parser.ExpectOptional("REPLICA IDENTITY"))
{
parser.Expect("NOTHING");
}
else
{
throw new TeamworkParserException("CannotParseStringUnsupportedCommand");
}
if (parser.ExpectOptional(";"))
{
break;
}
else
{
parser.Expect(",");
}
}
}
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(';');
}
}
