Creating Foreign Keys

[Troy Goode]

Hey guys - I'm finally getting around to switching from RikMigrations
to FluentMigrator and really liking what my team has seen so far. One
bump in the road is how Fluent seems to handle foreign keys:

Create.Table("Users")
.WithColumn("UserId").AsInt32().Identity().PrimaryKey()
.WithColumn("GroupId").AsInt32().NotNullable()
.WithColumn("UserName").AsString(32).NotNullable()
.WithColumn("Password").AsString(32).NotNullable();

Create.Table("Groups")
.WithColumn("GroupId").AsInt32().Identity().PrimaryKey()
.WithColumn("Name").AsString(32).NotNullable();

Create.ForeignKey("FK_Foo").FromTable("Users").ForeignColumn("GroupId").ToTable("Groups").PrimaryColumn("GroupId");

In RikMigrations I could've simplified this a bit by reversing the
order of the table definitions (since Users depends upon Groups) and
adding the foreign key directly to the GroupId column of the Users
table (what follows is FluentMigrator with Rik-style foreign keys):

Create.Table("Groups")
.WithColumn("GroupId").AsInt32().Identity().PrimaryKey()
.WithColumn("Name").AsString(32).NotNullable();

Create.Table("Users")
.WithColumn("UserId").AsInt32().Identity().PrimaryKey()
.WithColumn("GroupId").AsInt32().NotNullable().References("Groups",
"GroupId")
.WithColumn("UserName").AsString(32).NotNullable()
.WithColumn("Password").AsString(32).NotNullable();

As far as I can tell, this style isn't currently supported by
FluentMigrator, correct? Is there any reason why, or is it just
waiting for the first slob to ask to implement it? :-)

--
You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.
To post to this group, send email to fluentmigrato...@googlegroups.com.
To unsubscribe from this group, send email to fluentmigrator-goog...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/fluentmigrator-google-group?hl=en.

[Sean Chambers]

Big +1 from me.

We have a lo of noise around adding foreign keys in one of our apps
and this would save that.

I would go ahead and implement it as you have it and see how it works
out. I have a feeling there's something I'm forgetting but we can
cross that bridge if/when we get there. The only thing that may cause
a problem is mixing table creation with creating a foreign key and you
may have to dance around the api a bit.

In the start we should just add this in addition to the normal way as
part of our move towards 1.0, I have a feeling removing the existing
foreign key creation would break too many people.

Anyone else think Of anything?

Thanks!

Sean

[Josh Coffman]

I'm wondering about multi-db support with this approach.  Thinking that not all of our supported DB's will make it easy. (I'm looking at you mysql).  But I'm not sure, and it depends how you handle it.

@JoshCoffman
480-270-4578 | josh [at] computeristsolutions [dot] com | http://computeristsolutions.com

On Wed, May 5, 2010 at 2:44 PM, Sean Chambers <scham...@gmail.com> wrote:

Big +1 from me.

We have a lo of noise around adding foreign keys in one of our apps and this would save that.

I would go ahead and implement it as you have it and see how it works out. I have a feeling there's something I'm forgetting but we can cross that bridge if/when we get there. The only thing that may cause a problem is mixing table creation with creating a foreign key and you may have to dance around the api a bit.

In the start we should just add this in addition to the normal way as part of our move towards 1.0, I have a feeling removing the existing foreign key creation would break too many people.

Anyone else think Of anything?

Thanks!

Sean

On May 5, 2010, at 4:02 PM, Troy Goode <troy...@gmail.com> wrote:

Hey guys - I'm finally getting around to switching from RikMigrations
to FluentMigrator and really liking what my team has seen so far. One
bump in the road is how Fluent seems to handle foreign keys:

Create.Table("Users")
 .WithColumn("UserId").AsInt32().Identity().PrimaryKey()
 .WithColumn("GroupId").AsInt32().NotNullable()
 .WithColumn("UserName").AsString(32).NotNullable()
 .WithColumn("Password").AsString(32).NotNullable();

Create.Table("Groups")
 .WithColumn("GroupId").AsInt32().Identity().PrimaryKey()
 .WithColumn("Name").AsString(32).NotNullable();

Create.ForeignKey("FK_Foo").FromTable("Users").ForeignColumn("GroupId").ToTable("Groups").PrimaryColumn("GroupId");

In RikMigrations I could've simplified this a bit by reversing the
order of the table definitions (since Users depends upon Groups) and
adding the foreign key directly to the GroupId column of the Users
table (what follows is FluentMigrator with Rik-style foreign keys):

Create.Table("Groups")
 .WithColumn("GroupId").AsInt32().Identity().PrimaryKey()
 .WithColumn("Name").AsString(32).NotNullable();

Create.Table("Users")
 .WithColumn("UserId").AsInt32().Identity().PrimaryKey()
 .WithColumn("GroupId").AsInt32().NotNullable().References("Groups",
"GroupId")
 .WithColumn("UserName").AsString(32).NotNullable()
 .WithColumn("Password").AsString(32).NotNullable();

As far as I can tell, this style isn't currently supported by
FluentMigrator, correct? Is there any reason why, or is it just
waiting for the first slob to ask to implement it? :-)

--
You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.

To post to this group, send email to fluentmigrato...@googlegroups.com.
To unsubscribe from this group, send email to fluentmigrator-goog...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/fluentmigrator-google-group?hl=en.

--
You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.
To post to this group, send email to fluentmigrato...@googlegroups.com.
To unsubscribe from this group, send email to fluentmigrator-goog...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/fluentmigrator-google-group?hl=en.

[Troy Goode]

>> In the start we should just add this in addition to the normal way as
part of our move towards 1.0

Oh totally - you still need the current way in any case as it allows
you to create foreign keys on tables that were added in previous
migrations (can't think of a use case at the moment for that, but I'm
sure it would come up if we took it out).

>> I'm wondering about multi-db support with this approach

I don't really think this approach would be at all different to the
current approach in regard to multi-db support - it is just a more
fluent way of expressing the same thing. In fact, to the
IMigrationContext it should look the same:
- when you first call .CreateTable it will add the
CreateTableExpression to the IMigrationContext and returns a
CreateTableExpressionBuilder
- then, when you call .WithColumn on the table builder, it adds a
column to the previously created expression and returns the table
builder (with a column-adding-oriented interface)
- then, when you call ".References" on the table builder, it would
add a new CreateForeignKeyExpression to the IMigrationContext and
return the table builder with the column-adding-oriented interface

My only question is how to get the reference to the IMigrationContext
from the table builder? It looks like I may have to change the
CreateTableExpressionBuilder to have a reference to the context.

Sorry, most of that was me walking myself through the solution as I
learned the codebase. =)

Troy

On May 5, 5:44 pm, Sean Chambers <schamber...@gmail.com> wrote:
> Big +1 from me.
>
> We have a lo of noise around adding foreign keys in one of our apps  
> and this would save that.
>
> I would go ahead and implement it as you have it and see how it works  
> out. I have a feeling there's something I'm forgetting but we can  
> cross that bridge if/when we get there. The only thing that may cause  
> a problem is mixing table creation with creating a foreign key and you  
> may have to dance around the api a bit.
>
> In the start we should just add this in addition to the normal way as  
> part of our move towards 1.0, I have a feeling removing the existing  
> foreign key creation would break too many people.
>
> Anyone else think Of anything?
>
> Thanks!
>
> Sean
>

> > For more options, visit this group athttp://groups.google.com/group/fluentmigrator-google-group?hl=en

[Troy Goode]

Okay, I went ahead and implemented it. It did indeed require passing
the IMigrationContext to the constructors of
CreateTableExpressionBuilder and CreateColumnExpressionBuilder, but I
don't think thats such a bad thing. Here is the commit:

http://github.com/TroyGoode/fluentmigrator/commit/3624762c9dd28ad051a5bdbf72db07661e151ba6

Note: My fork is upgraded to .Net4 & VS2010. I'd recommend cherry-
picking this commit unless you're ready to upgrade the whole shebang.

[Sean Chambers]

Awesome. I'll look over it tomorrow and pull it in.

Does going to vs2010/.net 4 breaking anyone that is using 2008? If not then I'll push that as well.

Thanks Troy!

Sean

Sent from my iPad

[Troy Goode]

>> Does going to vs2010/.net 4 breaking anyone that is using 2008? If not then I'll push that as well.

It does, unfortunately. Just going to VS2010 wouldn't, as we could
still downcompile to 3.5. Perhaps that is what I should've done, but
at the very least I needed the FluentMigrator.Console app to be
converted to .NET4 so that it could host our .NET4 migration
assemblies at work (a .NET 3.5 process cannot host .NET 4 assemblies)
so I went ahead and converted the rest as well.

The good news is I didn't have to change any code (just .sln
and .csproj files) during the upgrade, so you should still be able to
safely pull other commits from me without negatively impacting the
project.

On May 5, 11:08 pm, Sean Chambers <schamber...@gmail.com> wrote:
> Awesome. I'll look over it tomorrow and pull it in.
>
> Does going to vs2010/.net 4 breaking anyone that is using 2008? If not then I'll push that as well.
>
> Thanks Troy!
>
> Sean
>
> Sent from my iPad
>

> On May 5, 2010, at 10:56 PM, Troy Goode <troygo...@gmail.com> wrote:
>
>
>
>
>
> > Okay, I went ahead and implemented it. It did indeed require passing
> > the IMigrationContext to the constructors of
> > CreateTableExpressionBuilder and CreateColumnExpressionBuilder, but I
> > don't think thats such a bad thing. Here is the commit:
>

> >http://github.com/TroyGoode/fluentmigrator/commit/3624762c9dd28ad051a...

[Sean Chambers]

Ok cool. If the changes in the csproj files can get in, then we can always slice off a different sln for 4.0 consumers. There may be more to it than I'm seeing however.

Sent from my iPad