/// <summary>
/// Parses COMMENT ON INDEX.
/// </summary>
private static void ParseIndex(Parser parser, PgDatabase database)
{
var indexName = parser.ParseIdentifier();
var objectName = ParserUtils.GetObjectName(indexName);
var schemaName = ParserUtils.GetSchemaName(indexName, database);
if (database.SchemaIsIgnored(schemaName))
{
return;
}
PgSchema schema = database.GetSchema(schemaName);
PgIndex index = schema.GetIndex(objectName);
if (index == null)
{
PgConstraint primaryKey = schema.GetPrimaryKey(objectName);
parser.Expect("IS");
primaryKey.Comment = GetComment(parser);
parser.Expect(";");
}
else
{
parser.Expect("IS");
index.Comment = GetComment(parser);
parser.Expect(";");
}
}
/// <summary>
/// Outputs statements for creation of new indexes.
/// </summary>
public static void Create(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (PgTable newTable in newSchema.Tables)
{
// Add new indexes
if (oldSchema == null)
{
foreach (PgIndex index in newTable.Indexes)
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(index.CreationSQL);
}
}
else
{
foreach (PgIndex index in GetNewIndexes(oldSchema.GetTable(newTable.Name), newTable))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(index.CreationSQL);
}
}
}
}
/// <summary>
/// Parses CREATE SCHEMA statement.
/// </summary>
public static void Parse(PgDatabase database, string statement)
{
var parser = new Parser(statement);
parser.Expect("CREATE", "SCHEMA");
if (parser.ExpectOptional("AUTHORIZATION"))
{
var schema = new PgSchema(ParserUtils.GetObjectName(parser.ParseIdentifier()));
database.AddSchema(schema);
schema.Authorization = schema.Name;
var definition = parser.Rest();
if (definition != null && definition.Length > 0)
{
schema.Definition = definition;
}
}
else
{
var schema = new PgSchema(ParserUtils.GetObjectName(parser.ParseIdentifier()));
database.AddSchema(schema);
if (parser.ExpectOptional("AUTHORIZATION"))
{
schema.Authorization = ParserUtils.GetObjectName(parser.ParseIdentifier());
}
var definition = parser.Rest();
if (definition != null && definition.Length > 0)
{
schema.Definition = definition;
}
}
}
public static void CreateIndexes(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (var newTable in newSchema.GetTables())
{
var newTableName = newTable.Name;
// Add new indexes
if (oldSchema == null)
{
foreach (var index in newTable.GetIndexes())
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(index.GetCreationSql());
}
}
else
{
foreach (var index in GetNewIndexes(
oldSchema.GetTable(newTableName), newTable))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(index.GetCreationSql());
}
}
}
}
/// <summary>
/// Adds <see cref="PgPrivilegeCommand.Grant"/> and <see cref="PgPrivilegeCommand.Revoke"/> privileges.
/// </summary>
public static void Create(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
// Add new sequences
foreach (var privilege in newSchema.Privileges)
{
if (oldSchema == null || !oldSchema.ContainsPrivilege(privilege))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(BetaMessage);
writer.WriteLine(privilege.Create());
}
}
if (oldSchema == null)
{
return;
}
// revert privileges
foreach (var privilege in oldSchema.Privileges)
{
if (!newSchema.ContainsPrivilege(privilege))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(BetaMessage);
writer.WriteLine(privilege.CreateRevert());
}
}
}
/// <summary>
/// Outputs statements for dropping tables.
/// </summary>
public static void Drop(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
if (oldSchema == null)
{
return;
}
var referencedTables = new Dictionary <string, List <PgTable> >();
var dropedTables = new List <PgTable>();
foreach (PgTable table in oldSchema.Tables)
{
if (!newSchema.ContainsTable(table.Name))
{
searchPathHelper.OutputSearchPath(writer);
dropedTables.Add(table);
foreach (var constraint in table.Constraints)
{
var regex = new Regex(@"FOREIGN KEY.+REFERENCES (?<ReferencedTable>.+)\(.+\)");
MatchCollection matchCollection = regex.Matches(constraint.Definition);
if (matchCollection.Count > 0)
{
var referencedTable = matchCollection[0].Groups["ReferencedTable"].Value;
if (referencedTables.ContainsKey(referencedTable))
{
if (referencedTables.TryGetValue(referencedTable, out List <PgTable> tables))
{
tables.Add(table);
}
}
else
{
referencedTables.Add(referencedTable, new List <PgTable>()
{
table
});
}
}
}
}
}
List <PgTable> dropableTables = null;
while (dropableTables == null)
{
dropableTables = SortTablesByReferences(referencedTables, dropedTables);
}
foreach (PgTable table in dropableTables)
{
writer.WriteLine();
writer.WriteLine(table.DropSQL);
}
}
public void TestParseSchemaThreeQuoted()
{
var database = new PgDatabase();
var schema = new PgSchema("juzz_system");
database.Schemas.Add(schema);
Assert.AreEqual(ParserUtils.GetSchemaName("\"juzz_system\".\"f_obj_execute_node_select\".\"test\"", database), "juzz_system");
}
public static void Parse(PgDatabase database, string statement)
{
var parser = new Parser(statement);
parser.Expect("CREATE");
parser.ExpectOptional("OR", "REPLACE");
parser.Expect("RULE");
var ruleIdentifier = parser.ParseIdentifier();
var rule = new PgRule(ParserUtils.GetObjectName(ruleIdentifier));
var schemaName = ParserUtils.GetSchemaName(ruleIdentifier, database);
if (database.SchemaIsIgnored(schemaName))
{
return;
}
parser.Expect("AS", "ON");
rule.EventType = parser.ExpectOptionalOneOf("SELECT", "INSERT", "UPDATE", "DELETE");
parser.Expect("TO");
var tableIdentifier = parser.ParseIdentifier();
if (parser.ExpectOptional("WHERE"))
{
var endIndex = parser.String.IndexOf("DO");
rule.Condition = parser.GetSubString(0, endIndex);
}
rule.TableName = ParserUtils.GetObjectName(tableIdentifier);
parser.Expect("DO");
rule.Do = parser.ExpectOptionalOneOf("ALSO", "INSTEAD");
rule.Command = parser.Rest() + ";";
PgSchema schema = database.GetSchema(schemaName);
if (schema == null)
{
throw new Exception(string.Format("CannotFindSchema {0}. Statement {1}", schemaName, statement));
}
// check if the rule is a view
if (rule.EventType == "SELECT" && rule.Do == "INSTEAD")
{
var table = schema.Tables.Single(t => t.Name == rule.TableName);
schema.Tables.Remove(table);
schema.Views.Add(new PgView(table.Name)
{
Comment = table.Comment, Query = rule.Command
});
}
else
{
schema.Rules.Add(rule);
}
}
public static void AlterComments(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
if (oldSchema == null)
{
return;
}
foreach (var oldTable in oldSchema.GetTables())
{
var newTable = newSchema.GetTable(oldTable.Name);
if (newTable == null)
{
continue;
}
foreach (var oldTrigger in oldTable.GetTriggers())
{
var newTrigger =
newTable.GetTrigger(oldTrigger.Name);
if (newTrigger == null)
{
continue;
}
if (oldTrigger.Comment == null &&
newTrigger.Comment != null ||
oldTrigger.Comment != null &&
newTrigger.Comment != null &&
!oldTrigger.Comment.Equals(
newTrigger.Comment))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("COMMENT ON TRIGGER ");
writer.Write(PgDiffUtils.GetQuotedName(newTrigger.Name));
writer.Write(" ON ");
writer.Write(PgDiffUtils.GetQuotedName(newTrigger.TableName));
writer.Write(" IS ");
writer.Write(newTrigger.Comment);
writer.WriteLine(';');
}
else if (oldTrigger.Comment != null && newTrigger.Comment == null)
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("COMMENT ON TRIGGER ");
writer.Write(PgDiffUtils.GetQuotedName(newTrigger.Name));
writer.Write(" ON ");
writer.Write(PgDiffUtils.GetQuotedName(newTrigger.TableName));
writer.WriteLine(" IS NULL;");
}
}
}
}
/// <summary>
/// Parses COMMENT ON SCHEMA.
/// </summary>
private static void ParseSchema(Parser parser, PgDatabase database)
{
var schemaName = ParserUtils.GetObjectName(parser.ParseIdentifier());
PgSchema schema = database.GetSchema(schemaName);
parser.Expect("IS");
schema.Comment = GetComment(parser);
parser.Expect(";");
}
/// <summary>
/// Outputs statements for creation of new tables.
/// </summary>
public static void Create(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (PgTable table in newSchema.Tables)
{
if (oldSchema == null || !oldSchema.ContainsTable(table.Name))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(table.CreationSQL);
}
}
}
/// <summary>
/// Outputs statements for trigger 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 (PgTrigger oldTrigger in oldTable.Triggers)
{
PgTrigger newTrigger = newTable.GetTrigger(oldTrigger.Name);
if (newTrigger == null)
{
continue;
}
if ((oldTrigger.Comment == null && newTrigger.Comment != null) ||
(oldTrigger.Comment != null &&
newTrigger.Comment != null &&
!oldTrigger.Comment.Equals(newTrigger.Comment)))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("COMMENT ON TRIGGER ");
writer.Write(PgDiffStringExtension.QuoteName(newTrigger.Name));
writer.Write(" ON ");
writer.Write(PgDiffStringExtension.QuoteName(newTrigger.TableName));
writer.Write(" IS ");
writer.Write(newTrigger.Comment);
writer.WriteLine(';');
}
else if (oldTrigger.Comment != null && newTrigger.Comment == null)
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.Write("COMMENT ON TRIGGER ");
writer.Write(PgDiffStringExtension.QuoteName(newTrigger.Name));
writer.Write(" ON ");
writer.Write(PgDiffStringExtension.QuoteName(newTrigger.TableName));
writer.WriteLine(" IS NULL;");
}
}
}
}
/// <summary>
/// Outputs statements for creation of views.
/// </summary>
public static void Create(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (PgView newView in newSchema.Views)
{
if (oldSchema == null || !oldSchema.ContainsView(newView.Name) || IsViewModified(oldSchema.GetView(newView.Name), newView))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(newView.CreationSQL);
}
}
}
/// <summary>
/// Outputs statements for creation of new <see cref="PgRule"/>s.
/// </summary>
internal static void Create(StreamWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (var rule in newSchema.Rules)
{
if (oldSchema == null || !oldSchema.Contains(rule))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(rule.CreationSql);
}
}
}
public static void AlterCreatedSequences(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
// Alter created sequences
foreach (var sequence in newSchema.GetSequences())
{
if ((oldSchema == null || !oldSchema.ContainsSequence(sequence.Name)) && !string.IsNullOrEmpty(sequence.OwnedBy))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(sequence.GetOwnedBySql());
}
}
}
public static void CreateSequences(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
// Add new sequences
foreach (var sequence in newSchema.GetSequences())
{
if (oldSchema == null || !oldSchema.ContainsSequence(sequence.Name))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(sequence.GetCreationSql());
}
}
}
public static void CreateTables(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (var table in newSchema.GetTables())
{
if (oldSchema == null ||
!oldSchema.ContainsTable(table.Name))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(table.GetCreationSql());
}
}
}
/// <summary>
/// Outputs statements for creation of new sequences.
/// </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 sequences
foreach (PgSequence sequence in newSchema.Sequences)
{
if (oldSchema == null || !oldSchema.ContainsSequence(sequence.Name))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(sequence.CreationSQL);
}
}
}
/// <summary>
/// Outputs statements for altering of new sequences.
/// </summary>
/// <param name="writer">Writer the output should be written to.</param>
/// <param name="oldSchema">The old schema.</param>
/// <param name="newSchema">The new schema.</param>
/// <param name="searchPathHelper">The search path helper.</param>
public static void AlterCreatedSequences(StreamWriter writer, [NullGuard.AllowNull] PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
// Alter created sequences
foreach (PgSequence sequence in newSchema.Sequences)
{
if ((oldSchema == null || !oldSchema.ContainsSequence(sequence.Name)) && sequence.Owner != null && sequence.Owner != string.Empty)
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(sequence.OwnedBySQL);
}
}
}
/// <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);
}
/// <summary>
/// Parses CREATE INDEX statement.
/// </summary>
public static void Parse(PgDatabase database, string statement)
{
var parser = new Parser(statement);
parser.Expect("CREATE");
var unique = parser.ExpectOptional("UNIQUE");
parser.Expect("INDEX");
parser.ExpectOptional("CONCURRENTLY");
var indexName = ParserUtils.GetObjectName(parser.ParseIdentifier());
parser.Expect("ON");
var tableName = parser.ParseIdentifier();
var definition = parser.Rest();
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)
{
throw new TeamworkParserException($"CannotFindTable {tableName} from {statement}");
}
var index = new PgIndex(indexName);
table.AddIndex(index);
schema.Add(index);
index.Definition = definition.Trim();
index.TableName = table.Name;
index.Unique = unique;
}
public static void DropTriggers(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (var newTable in newSchema.GetTables())
{
var oldTable = oldSchema?.GetTable(newTable.Name);
// Drop triggers that no more exist or are modified
foreach (var trigger in GetDropTriggers(oldTable, newTable))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(trigger.GetDropSql());
}
}
}
public static void CreateTriggers(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (var newTable in newSchema.GetTables())
{
var oldTable = oldSchema?.GetTable(newTable.Name);
// Add new triggers
foreach (var trigger in GetNewTriggers(oldTable, newTable))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(trigger.GetCreationSql());
}
}
}
public static void CreateConstraints(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, bool primaryKey, SearchPathHelper searchPathHelper)
{
foreach (var newTable in newSchema.GetTables())
{
var oldTable = oldSchema?.GetTable(newTable.Name);
// Add new constraints
foreach (var constraint in GetNewConstraints(oldTable, newTable, primaryKey))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(constraint.GetCreationSql());
}
}
}
public static void DropConstraints(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, bool primaryKey, SearchPathHelper searchPathHelper)
{
foreach (var newTable in newSchema.GetTables())
{
var oldTable = oldSchema?.GetTable(newTable.Name);
// Drop constraints that no more exist or are modified
foreach (var constraint in GetDropConstraints(oldTable, newTable, primaryKey))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(constraint.GetDropSql());
}
}
}
public static void CreateViews(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (var newView in newSchema.GetViews())
{
if (oldSchema == null ||
!oldSchema.ContainsView(newView.Name) ||
IsViewModified(
oldSchema.GetView(newView.Name), newView))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(newView.GetCreationSql());
}
}
}
public static void CreateFunctions(TextWriter writer, PgDiffArguments arguments,
PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
// Add new functions and replace modified functions
foreach (var newFunction in newSchema.GetFunctions())
{
var oldFunction = oldSchema?.GetFunction(newFunction.GetSignature());
if (oldFunction == null || !newFunction.Equals(oldFunction, arguments.IgnoreFunctionWhitespace))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(newFunction.GetCreationSql());
}
}
}
public static void DropIndexes(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
foreach (var newTable in newSchema.GetTables())
{
var newTableName = newTable.Name;
var oldTable = oldSchema?.GetTable(newTableName);
// Drop indexes that do not exist in new schema or are modified
foreach (var index in GetDropIndexes(oldTable, newTable))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(index.GetDropSql());
}
}
}
public static void DropTables(TextWriter writer, PgSchema oldSchema, PgSchema newSchema, SearchPathHelper searchPathHelper)
{
if (oldSchema == null)
{
return;
}
foreach (var table in oldSchema.GetTables())
{
if (!newSchema.ContainsTable(table.Name))
{
searchPathHelper.OutputSearchPath(writer);
writer.WriteLine();
writer.WriteLine(table.GetDropSql());
}
}
}
