I have installed dcpostgresql68pro.exe and PostgreSQL 9.4.4
And wrote a program that extracts migration SQL code at run-time, so I could run it manually, instead of just applying migration automatically.
The problem is that the extracted code differs from that which is generated by this command(and it is incorrect)
Code: Select all
Update-Database -Script -TargetMigration:"InitialCreate"
Code: Select all
CREATE TABLE public.rb_group (
rb_group_id serial NOT NULL,
rb_group_name varchar(50) NOT NULL,
rb_group_description varchar(300) NOT NULL,
PRIMARY KEY (rb_group_id)
);
CREATE UNIQUE INDEX unique_key_rb_group_names ON public.rb_group (rb_group_name);
CREATE TABLE public.reference_book (
rb_id serial NOT NULL,
rb_group_id int NULL,
rb_name varchar(50) NOT NULL,
rb_description varchar(300) NOT NULL,
PRIMARY KEY (rb_id)
);
ALTER TABLE public.reference_book
ADD CONSTRAINT "FK_reference_book_rb_group_rb_group_id" FOREIGN KEY (rb_group_id) REFERENCES public.rb_group ()
ON DELETE NO ACTION;
CREATE INDEX "IX_reference_book_rb_group_id" ON public.reference_book (rb_group_id);
CREATE UNIQUE INDEX unique_key_reference_book ON public.reference_book (rb_name);
Code: Select all
CREATE TABLE public.rb_group (
rb_group_id serial NOT NULL,
rb_group_name varchar(50) NOT NULL,
rb_group_description varchar(300) NOT NULL,
PRIMARY KEY (rb_group_id)
);
CREATE UNIQUE INDEX unique_key_rb_group_names ON public.rb_group (rb_group_name);
CREATE TABLE public.reference_book (
rb_id serial NOT NULL,
rb_group_id int NULL,
rb_name varchar(50) NOT NULL,
rb_description varchar(300) NOT NULL,
PRIMARY KEY (rb_id)
);
CREATE INDEX "IX_reference_book_rb_group_id" ON public.reference_book (rb_group_id);
CREATE UNIQUE INDEX unique_key_reference_book ON public.reference_book (rb_name);
ALTER TABLE public.reference_book
ADD CONSTRAINT "FK_reference_book_rb_group_rb_group_id" FOREIGN KEY (rb_group_id) REFERENCES public.rb_group (rb_group_id)
ON DELETE NO ACTION;
Here is my program
Code: Select all
class SQLGenerator
{
public static void Gen(Form1 f1)
{
var migration_InitialCreate = new InitialCreate();
migration_InitialCreate.Up();
System.Diagnostics.Debug.Write(RunMigration(migration_InitialCreate));
}
public static string RunMigration(DbMigration migration, bool run_sql = false)
{
string genSQL = "";
var prop = migration.GetType().GetProperty("Operations", BindingFlags.NonPublic | BindingFlags.Instance);//, BindingFlags.NonPublic | BindingFlags.Instance
if (prop != null)
{
IEnumerable<MigrationOperation> operations = prop.GetValue(migration) as IEnumerable<MigrationOperation>;
var generator = new Devart.Data.PostgreSql.Entity.Migrations.PgSqlEntityMigrationSqlGenerator();
var statements = generator.Generate(operations, "PostgreSQL, 9.4.4");
using (SQLGen_DB_Context db = new SQLGen_DB_Context())
{
db.Database.Connection.Open();
foreach (MigrationStatement item in statements)
{
if (genSQL != "") genSQL += "\r\n";
genSQL += item.Sql + ";\r\n";
if (run_sql == true)
{
try
{
db.Database.ExecuteSqlCommand(item.Sql);
}
catch (Exception ex)
{
string ex_message = ex.Message;
System.Diagnostics.Debug.WriteLine("Error\r\n " + item.Sql + "\r\n" + ex_message);
}
}
}
}
}
return genSQL;
}
public class rb_group
{
[Key]
public int rb_group_id { get; set; }
[Required, StringLength(50)]
[Index("unique_key_rb_group_names", IsUnique = true)]
public string rb_group_name { get; set; }
[Required, StringLength(300)]
public string rb_group_description { get; set; }
public virtual ICollection<reference_book> reference_books { get; set; }
}
public class reference_book
{
[Key]
public int rb_id { get; set; }
public Nullable<int> rb_group_id { get; set; }
[Required, StringLength(50)]
[Index("unique_key_reference_book", Order = 1, IsUnique = true)]
public string rb_name { get; set; }
[Required, StringLength(300)]
public string rb_description { get; set; }
public virtual rb_group rb_group { get; set; }
}
public class SQLGen_DB_Context : DbContext
{
public SQLGen_DB_Context()
: base("DefaultConnection")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// If you are letting EntityFrameowrk to create the database,
// it will by default create the __MigrationHisotry table in the dbo schema
// Use HasDefaultSchema to specify alternative (i.e public) schema
modelBuilder.HasDefaultSchema("public");
}
public DbSet<rb_group> rb_groups { get; set; }
public DbSet<reference_book> reference_books { get; set; }
}
}
Code: Select all
namespace SQLGenerator.Migrations
{
using System;
using System.Data.Entity.Migrations;
public partial class InitialCreate : DbMigration
{
public override void Up()
{
CreateTable(
"public.rb_group",
c => new
{
rb_group_id = c.Int(nullable: false, identity: true),
rb_group_name = c.String(nullable: false, maxLength: 50),
rb_group_description = c.String(nullable: false, maxLength: 300),
})
.PrimaryKey(t => t.rb_group_id)
.Index(t => t.rb_group_name, unique: true, name: "unique_key_rb_group_names");
CreateTable(
"public.reference_book",
c => new
{
rb_id = c.Int(nullable: false, identity: true),
rb_group_id = c.Int(),
rb_name = c.String(nullable: false, maxLength: 50),
rb_description = c.String(nullable: false, maxLength: 300),
})
.PrimaryKey(t => t.rb_id)
.ForeignKey("public.rb_group", t => t.rb_group_id)
.Index(t => t.rb_group_id)
.Index(t => t.rb_name, unique: true, name: "unique_key_reference_book");
}
public override void Down()
{
DropForeignKey("public.reference_book", "rb_group_id", "public.rb_group");
DropIndex("public.reference_book", "unique_key_reference_book");
DropIndex("public.reference_book", new[] { "rb_group_id" });
DropIndex("public.rb_group", "unique_key_rb_group_names");
DropTable("public.reference_book");
DropTable("public.rb_group");
}
}
}
Code: Select all
namespace SQLGenerator.Migrations
{
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
internal sealed class Configuration : DbMigrationsConfiguration<SQLGenerator.SQLGen_DB_Context>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
SetSqlGenerator(Devart.Data.PostgreSql.Entity.Migrations.PgSqlConnectionInfo.InvariantName, new Devart.Data.PostgreSql.Entity.Migrations.PgSqlEntityMigrationSqlGenerator());
}
protected override void Seed(SQLGenerator.SQLGen_DB_Context context)
{
// This method will be called after migrating to the latest version.
// You can use the DbSet<T>.AddOrUpdate() helper extension method
// to avoid creating duplicate seed data. E.g.
//
// context.People.AddOrUpdate(
// p => p.FullName,
// new Person { FullName = "Andrew Peters" },
// new Person { FullName = "Brice Lambson" },
// new Person { FullName = "Rowan Miller" }
// );
//
}
}
}