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(';'); } }