Entity Framework Migrations for the NetIQ JDBC Driver

Database Migrations

Many high-level development frameworks employ the concept of Database Migrations when it comes to maintaining a database schema. Notable examples include Active Record (used primarily by Ruby on Rails) and Entity Framework (used primarily by ASP.NET). When using Database Migrations, simple code files are created – usually automatically – in the native language of the platform. In the case of Entity Framework this is C# or VB. With Active Record it’s Ruby. These code files use database-agnostic language constructs to describe schema changes. These frameworks also include tools for both creating these migration files and for playing them against a target database, effectively bringing that database up-to-date.

There are many gains to be seen by using such a system:

  1. Scripting changes to the schema is generally automatic
  2. Bringing a database up-to-date is automated
  3. Schema changes are easy to diff and version-control
  4. Schema definition and maintenance simplified and formalized

As you can see, using Database Migrations is A Good Thingtm.

NetIQ Integration

Interfacing with NetIQ can be done using their JDBC Driver. By utilizing their JDBC Driver, integrating with NetIQ is more-or-less a matter of providing data from your database using the correct schema, either directly or using a view. And that data consists primarily of two tables: an Event Queue table and an Event History table. Once the JDBC Driver is properly configured you can write to the Event Queue table in order to send data to NetIQ.

And, while the sample projects and documentation for the NetIQ JDBC driver contain example SQL, but if you are using Entity Framework you’ll want to use Database Migrations in order to create and maintain your schema.

NetIQ Database Migrations

To get started adding the NetIQ migrations to your Visual Studio project, click Tools, NuGet Package Manager and then Package Manager Console.

If you do not currently have any migrations for your project (check for a Migrations folder in your project), enter the following commands, one-by-one, into the NuGet Package Manager Console:

  • Enable-Migrations
  • Add-Migration CreateInitialSchema
  • Update-Database

Next, for all users, enter the following command:

  • Add-Migration CreateNetIQObjects

Paste in the following class definition:

public partial class CreateNetIQObjects : DbMigration {
    public override void Up()
    {
        CreateEventTable("EventQueue", true, true);
        CreateEventTable("EventHistory", false, false);
    }

    public override void Down()
    {
        DropTable("EventQueue");
        DropTable("EventHistory");
    }

    private void CreateEventTable(string tableName, bool indexes, bool constraints)
    {
        CreateTable(
            tableName,
            c => new
            {
                record_id = c.Decimal(nullable: false, precision: 28, scale: 0, storeType: "numeric", identity: true),
                table_key = c.String(nullable: false, maxLength: 64, unicode: false),
                status = c.String(nullable: false, maxLength: 1, fixedLength: true, unicode: false, defaultValue: constraints ? "N" : null),
                event_type = c.Byte(nullable: false),
                event_time = c.DateTime(nullable: false, defaultValueSql: constraints ? "getdate()" : null),
                table_name = c.String(nullable: false, maxLength: 64, unicode: false),
                perpetrator = c.String(maxLength: 64, unicode: false, defaultValueSql: constraints ? "user_name()" : null),
                column_name = c.String(maxLength: 64, unicode: false),
                old_value = c.String(maxLength: 64),
                new_value = c.String(maxLength: 64),
            }
        )
        .PrimaryKey(t => new { t.record_id } );

        if (constraints)
        {
            Sql(String.Format("ALTER TABLE {0} WITH CHECK ADD CONSTRAINT [chk_{0}_event_type] CHECK (([event_type]=(8) OR [event_type]=(7) OR [event_type]=(6) OR [event_type]=(5) OR [event_type]=(4) OR [event_type]=(3) OR [event_type]=(2) OR [event_type]=(1)))", tableName));
            Sql(String.Format("ALTER TABLE {0} CHECK CONSTRAINT [chk_{0}_event_type]", tableName));
            Sql(String.Format("ALTER TABLE {0} WITH CHECK ADD CONSTRAINT [chk_{0}_istatus] CHECK (([status]='F' OR [status]='E' OR [status]='W' OR [status]='S' OR [status]='N'))", tableName));
            Sql(String.Format("ALTER TABLE {0} CHECK CONSTRAINT [chk_{0}_istatus]", tableName));
        }

        if (indexes)
        {
            CreateIndex(tableName, new[] { "event_time", "record_id" });
            CreateIndex(tableName, new[] { "status" });
        }
    }
}

Finally, enter the following command in the Package Manager Console:

  • Update-Database

If you examine your target database you will see two new tables: EventQueue and EventHistory. And, if you are using Microsoft SQL Server Management Studio, you can use the right-click context menu to generate CREATE scripts for the new tables. Finally, you can compare these SQL scripts to those found with the NetIQ documentation for completeness.

 

Questions, comments or concerns? Feel free to reach out to us below or at IDMWORKS