/// <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(";");
}
}
public void RevokeParseTest()
{
var db = new PgDatabase("Name", new List <string>());
db.DefaultSchema.Privileges.Count.Should().Be(0);
PrivilegeParser.Parse(db, "REVOKE ALL ON FUNCTION generate_csharp_pocos() FROM PUBLIC;", PgPrivilegeCommand.Revoke);
db.DefaultSchema.Privileges.Count.Should().Be(1);
var priv = db.DefaultSchema.Privileges[0];
priv.Command.Should().Be(PgPrivilegeCommand.Revoke);
priv.Privilege.Should().Be(PgPrivilegeKind.All);
priv.Role.Should().Be("public");
priv.OnType.Should().Be("FUNCTION");
priv.OnName.Should().Be("generate_csharp_pocos()");
PrivilegeParser.Parse(db, "REVOKE ALL ON FUNCTION generate_ids(sequence_name text, count integer) FROM PUBLIC;", PgPrivilegeCommand.Revoke);
db.DefaultSchema.Privileges.Count.Should().Be(2);
priv = db.DefaultSchema.Privileges[1];
priv.Command.Should().Be(PgPrivilegeCommand.Revoke);
priv.Privilege.Should().Be(PgPrivilegeKind.All);
priv.Role.Should().Be("public");
priv.OnType.Should().Be("FUNCTION");
priv.OnName.Should().Be("generate_ids(sequence_name text, count integer)");
PrivilegeParser.Parse(db, "REVOKE ALL ON FUNCTION postgrestype_to_csharptype(pg_type text) FROM postgres;", PgPrivilegeCommand.Revoke);
db.DefaultSchema.Privileges.Count.Should().Be(3);
priv = db.DefaultSchema.Privileges[2];
priv.Command.Should().Be(PgPrivilegeCommand.Revoke);
priv.Privilege.Should().Be(PgPrivilegeKind.All);
priv.Role.Should().Be("postgres");
priv.OnType.Should().Be("FUNCTION");
priv.OnName.Should().Be("postgrestype_to_csharptype(pg_type text)");
}
public AggregateForm(PgDatabase database, string tableName)
{
InitializeComponent();
this.database = database;
this.tableName = tableName;
}
public ColumnsSelectModifier(PgDatabase database)
{
InitializeComponent();
this.tableNames = new List <string>();
Database = database;
}
/// <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 PgDatabase LoadDatabaseSchema(TextReader reader, string charsetName, bool outputIgnoredStatements, bool ignoreSlonyTriggers)
{
var database = new PgDatabase();
var statement = GetWholeStatement(reader);
while (statement != null)
{
if (PatternCreateSchema.IsMatch(statement))
{
CreateSchemaParser.Parse(database, statement);
}
else if (PatternDefaultSchema.IsMatch(statement))
{
database.SetDefaultSchema(PatternDefaultSchema.Matches(statement)[0].Groups[1].Value);
}
else if (PatternCreateTable.IsMatch(statement))
{
CreateTableParser.Parse(database, statement);
}
else if (PatternAlterTable.IsMatch(statement))
{
AlterTableParser.Parse(database, statement, outputIgnoredStatements);
}
else if (PatternCreateSequence.IsMatch(statement))
{
CreateSequenceParser.Parse(database, statement);
}
else if (PatternAlterSequence.IsMatch(statement))
{
AlterSequenceParser.Parse(database, statement, outputIgnoredStatements);
}
else if (PatternCreateIndex.IsMatch(statement))
{
CreateIndexParser.Parse(database, statement);
}
else if (PatternCreateView.IsMatch(statement))
{
CreateViewParser.Parse(database, statement);
}
else if (PatternAlterView.IsMatch(statement))
{
AlterViewParser.Parse(database, statement, outputIgnoredStatements);
}
else if (PatternCreateTrigger.IsMatch(statement))
{
CreateTriggerParser.Parse(database, statement, ignoreSlonyTriggers);
}
else if (PatternCreateFunction.IsMatch(statement))
{
CreateFunctionParser.Parse(database, statement);
}
else if (PatternComment.IsMatch(statement))
{
CommentParser.Parse(database, statement, outputIgnoredStatements);
}
else if (PatternSelect.IsMatch(statement) ||
PatternInsertInto.IsMatch(statement) ||
PatternUpdate.IsMatch(statement) ||
PatternDeleteFrom.IsMatch(statement)) /* we just ignore these statements*/ } {
/// <summary>
/// Tests the given <see cref="SQLFile"/> on the given <see cref="Database"/>.
/// </summary>
public void CreateData(Database database, SQLFile target)
{
if (database.CurrentVersion < target.Version)
{
throw new InvalidOperationException("The target file was not executed on the database");
}
// read file
PgDatabase postgresDatabase = PgDumpLoader.LoadDatabaseSchema(target.Path, database, false, false);
// create data for tables
foreach (var schema in postgresDatabase.Schemas)
{
var tables = new Dictionary <PgTable, bool>();
foreach (var table in schema.Tables)
{
tables.Add(table, false);
}
foreach (var table in schema.Tables)
{
this.CreateData(database, target, schema, table, tables);
}
}
}
static void Main(string[] args)
{
// Get our database connect data, will eventually be hard coded or accessed via a web API
DbConnectData data = DbConnectData.FromFile("DBConfig.txt.local");
// Create and init that database
PgDatabase database = new PgDatabase();
database.Init(data);
// Assign the database connection to the DA
DataAccess.Database = database;
// Make our winforms view manager and assign it to the view manager
ViewManager.Implementation = new WinformViewManager();
// Reflection load all our views
ViewManager.Implementation.ReflectLoadViews();
// Create our auth handler
Auth.Implementation = new NerdAuth();
Auth.User = null;
// Reflection load our rules and actions
LogicManager.LoadRules();
LogicManager.LoadActions();
// Run the application, starting at the Login form
ViewManager.Run("Login");
}
private static void ParseDatabase(Parser parser, PgDatabase database)
{
parser.ParseIdentifier();
parser.Expect("IS");
database.Comment = GetComment(parser);
parser.Expect(";");
}
public static string GetSchemaName(string name, PgDatabase database)
{
var names = SplitNames(name);
return(names.Length < 2
? database.DefaultSchema.Name
: names[0]);
}
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");
}
private static void ParseSchema(Parser parser, PgDatabase database)
{
var schemaName = ParserUtils.GetObjectName(parser.ParseIdentifier());
var schema = database.GetSchema(schemaName);
parser.Expect("IS");
schema.Comment = GetComment(parser);
parser.Expect(";");
}
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);
}
}
private static void DropOldSchemas(TextWriter writer, PgDatabase oldDatabase, PgDatabase newDatabase)
{
foreach (var oldSchema in oldDatabase.Schemas)
{
if (newDatabase.GetSchema(oldSchema.Name) == null)
{
writer.WriteLine();
writer.WriteLine($"DROP SCHEMA {PgDiffUtils.GetQuotedName(oldSchema.Name)} CASCADE;");
}
}
}
private static void CreateNewSchemas(TextWriter writer, PgDatabase oldDatabase, PgDatabase newDatabase)
{
foreach (var newSchema in newDatabase.Schemas)
{
if (oldDatabase.GetSchema(newSchema.Name) == null)
{
writer.WriteLine();
writer.WriteLine(newSchema.GetCreationSql());
}
}
}
private static void ParseTable(Parser parser, PgDatabase database)
{
var tableName = parser.ParseIdentifier();
var objectName = ParserUtils.GetObjectName(tableName);
var schemaName = ParserUtils.GetSchemaName(tableName, database);
var table = database.GetSchema(schemaName).GetTable(objectName);
parser.Expect("IS");
table.Comment = GetComment(parser);
parser.Expect(";");
}
public void TestEmptyMethods(Database database, SQLFile target)
{
if (database.CurrentVersion < target.Version)
{
throw new InvalidOperationException("The target file was not executed on the database");
}
// read file
PgDatabase oldDatabase = PgDumpLoader.LoadDatabaseSchema(target.Path, database, false, false);
// call empty functions
}
private static void ParseView(Parser parser, PgDatabase database)
{
var viewName = parser.ParseIdentifier();
var objectName = ParserUtils.GetObjectName(viewName);
var schemaName = ParserUtils.GetSchemaName(viewName, database);
var view = database.GetSchema(schemaName).GetView(objectName);
parser.Expect("IS");
view.Comment = GetComment(parser);
parser.Expect(";");
}
private void SetDatabase(string connectionString)
{
IConnection <NpgsqlConnection> connection = new PgConnection(connectionString);
connection = new LoggedConnection <NpgsqlConnection>(connection, logger);
database = new PgDatabase(connection);
view.Database = database;
columnsSelect.Database = database;
joinTable.Database = database;
pageSelector.Database = database;
tableSelect.Database = database;
}
internal static void CallCommentParserParseFunction(Parser parser, PgDatabase database)
{
var pt = new Microsoft.VisualStudio.TestTools.UnitTesting.PrivateType(typeof(CommentParser));
try
{
pt.InvokeStatic("ParseFunction", new object[] { parser, database });
}
catch (System.MissingMethodException missingMethodException)
{
throw new System.NotSupportedException("ParseFunction 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(";");
}
}
public static void Parse(PgDatabase database, string statement, bool outputIgnoredStatements)
{
var parser = new Parser(statement);
parser.Expect("COMMENT", "ON");
if (parser.ExpectOptional("TABLE"))
{
ParseTable(parser, database);
}
else if (parser.ExpectOptional("COLUMN"))
{
ParseColumn(parser, database);
}
else if (parser.ExpectOptional("CONSTRAINT"))
{
ParseConstraint(parser, database);
}
else if (parser.ExpectOptional("DATABASE"))
{
ParseDatabase(parser, database);
}
else if (parser.ExpectOptional("FUNCTION"))
{
ParseFunction(parser, database);
}
else if (parser.ExpectOptional("INDEX"))
{
ParseIndex(parser, database);
}
else if (parser.ExpectOptional("SCHEMA"))
{
ParseSchema(parser, database);
}
else if (parser.ExpectOptional("SEQUENCE"))
{
ParseSequence(parser, database);
}
else if (parser.ExpectOptional("TRIGGER"))
{
ParseTrigger(parser, database);
}
else if (parser.ExpectOptional("VIEW"))
{
ParseView(parser, database);
}
else if (outputIgnoredStatements)
{
database.IgnoredStatements.Add(statement);
}
}
/// <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;
}
/// <summary>
/// Parses ALTER SEQUENCE statement.
/// </summary>
public static void Parse(PgDatabase database, string statement)
{
var parser = new Parser(statement);
parser.Expect("ALTER", "SEQUENCE");
var sequenceName = parser.ParseIdentifier();
var schemaName = ParserUtils.GetSchemaName(sequenceName, database);
if (database.SchemaIsIgnored(schemaName))
{
return;
}
var schema = database.GetSchema(schemaName);
if (schema == null)
{
throw new TeamworkParserException($"CannotFindSchema {schemaName} from statement {statement}");
}
var objectName = ParserUtils.GetObjectName(sequenceName);
PgSequence sequence = schema.GetSequence(objectName);
if (sequence == null)
{
throw new TeamworkParserException($"CannotFindSequence {sequenceName} from statement {statement}");
}
while (!parser.ExpectOptional(";"))
{
if (parser.ExpectOptional("OWNED", "BY"))
{
if (parser.ExpectOptional("NONE"))
{
sequence.Owner = null;
}
else
{
sequence.Owner = parser.Expression();
}
}
else
{
throw new TeamworkParserException("CannotParseStringUnsupportedCommand");
}
}
}
private void DropOldSchemas(Database database, StreamWriter writer, PgDatabase oldDatabase, PgDatabase newDatabase)
{
foreach (PgSchema oldSchema in oldDatabase.Schemas)
{
if (newDatabase.GetSchema(oldSchema.Name) == null)
{
// ignore the ignored schemas
if (database.IgnoredSchemas.Contains(oldSchema.Name))
{
continue;
}
writer.WriteLine();
writer.WriteLine("DROP SCHEMA IF EXISTS " + oldSchema.Name.QuoteName() + " CASCADE;");
}
}
}
private void CreateNewSchemas(Database database, StreamWriter writer, PgDatabase oldDatabase, PgDatabase newDatabase)
{
foreach (PgSchema newSchema in newDatabase.Schemas)
{
// ignore the ignored schemas
if (database.IgnoredSchemas.Contains(newSchema.Name))
{
continue;
}
if (oldDatabase.GetSchema(newSchema.Name) == null)
{
writer.WriteLine();
writer.WriteLine(newSchema.CreationSQL);
}
}
}
/// <summary>
/// Parses COMMENT ON SEQUENCE.
/// </summary>
private static void ParseSequence(Parser parser, PgDatabase database)
{
var sequenceName = parser.ParseIdentifier();
var objectName = ParserUtils.GetObjectName(sequenceName);
var schemaName = ParserUtils.GetSchemaName(sequenceName, database);
if (database.SchemaIsIgnored(schemaName))
{
return;
}
PgSequence sequence = database.GetSchema(schemaName).GetSequence(objectName);
parser.Expect("IS");
sequence.Comment = GetComment(parser);
parser.Expect(";");
}
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);
var schema = database.GetSchema(schemaName);
var table = schema.GetTable(tableName);
if (table == null)
{
var 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
{
var column = table.GetColumn(objectName);
if (column == null)
{
throw new ParserException(string.Format(Resources.CannotFindColumnInTable, columnName, table.Name));
}
parser.Expect("IS");
column.Comment = GetComment(parser);
parser.Expect(";");
}
}
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);
}
