fluentmigrator bug in SqlServerProcessor.cs

[NanXiong]

checking index exists is not implemented correctly for sqlserver, it ignores the schema name the index should be in:

In SqlServerProcessor.cs, the 1st line (commented out by me) of the code is incorrect, something like the 2nd line should be used:

public override bool IndexExists(string schemaName, string tableName, string indexName)
{
//return Exists("SELECT NULL FROM sysindexes WHERE name = '{0}'", FormatSqlEscape(indexName));
return Exists("SELECT NULL FROM sysindexes WHERE name = '{0}' and object_id=OBJECT_ID('{1}.{2}')", FormatSqlEscape(indexName), SafeSchemaName(schemaName), FormatSqlEscape(tableName));
}

[daniellee]

Nice catch! I'll get this fixed and include it in the 1.0.4 release.

[NanXiong]

Thank you Daniel.

This is the code that I made it work:

public override bool IndexExists(string schemaName, string tableName, string indexName)
{
//return Exists("SELECT NULL FROM sysindexes WHERE name = '{0}'", FormatSqlEscape(indexName));

        string objectName = string.IsNullOrEmpty(schemaName) ? FormatSqlEscape(tableName) : SafeSchemaName(schemaName) + "." + FormatSqlEscape(tableName);

        return Exists("SELECT NULL FROM sys.indexes WHERE name = '{0}' and object_id=OBJECT_ID('{1}')", FormatSqlEscape(indexName), objectName);
    }

[ManfredLange]

This problem also results in a SqlException when executing the following code on SQL Azure as part of a migration:
if (!Schema.Table("VersionInfo").Index("PK_VersionInfo").Exists()) {
// ... some code ...
}
This code works on SQL Server 2012, though. Both SQL Server 2012 and SQL Azure have a system view "sys.indexes" so @NanXiong's suggested change should fix this.

In revision 98f3810 the line that needs to be changed is in file SqlServerProcessor.cs, line 82.

I'm using a local instance of SQL Server 2010 (reported as SQL Server 11.0.2100) and I'm using SQL Azure (reported as SQL Server 11.0.2065).

From MSFT's documentation regarding sys.sysindexes:
"This SQL Server 2000 system table is included as a view for backward compatibility." and also "Partitioned tables and indexes are not fully supported in this view; use the sys.indexes catalog view instead."
(Source: http://msdn.microsoft.com/en-us/library/ms190283.aspx)

As SQL Server 2012 has sys.sysindexes still around the above code sample works. On SQL Azure sys.sysindexes doesn't exist and we have to use sys.indexes.

[ManfredLange]

I can confirm that @NanXiong's suggestion (also) fixes the problem on SQL Azure. Just tested it in our staging environments on Azure. The dot makes the difference 'sys.indexes' works while 'sysindexes' doesn't.

[ManfredLange]

@daniellee The NuGet package version 1.0.4.0 still uses the SQL Server 2000 view 'sysindexes' instead of the current 'sys.indexes' which was supported from SQL Server 2005 forward and also works on SQL Azure.

[daniellee]

I'm travelling for the next 2 weeks but I can fix it when I get back if no one else has time before that.

[ManfredLange]

Hi Daniel,

thank you for the prompt response. I don't have commit permissions so can't
do the fix myself. I have a workaround for my own projects so will be fine
until the time when you are back and look into it.

We have been using Fluent Migrator for about a year in several cloud based
projects with very good results. The majority of the projects are part of a
collaboration with an business incubator where speed matters when you want
to try out new product versions. Using Fluent Migrator to speed up schema
migration has been a big help for the entrepreneurs. Thank you to all of
you for your fantastic work!

Thank you again. Let me know if I can be of further assistance.

Kind regards,
Manfred.

Kind regards,

Manfred.

Manfred Lange
Read my blog On Agile Leadership http://agileleadership.blogspot.com - Like
my company on Facebook http://www.facebook.com/AgileUtilities - Follow me
on Twitter http://twitter.com/go4agile

On Thu, Nov 15, 2012 at 10:05 PM, Daniel Lee notifications@github.comwrote:

I'm travelling for the next 2 weeks but I can fix it when I get back if no
one else has time before that.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/315#issuecomment-10401533.

[tommarien]

Hey all,

It doesn't have to wait until Daniel is back :), I'll focus on this tonight

Regards

[tommarien]

Thanks for remarking this issue, fixed in version 1.0.5 ;) which is released as of now

[ManfredLange]

Hi Tom,

Fantastic! Thank you for the quick turnaround.

We'll test in the next couple of days and report back. Our tests cover
deployments on SQL Server 2012, SQL Azure and Postgres 9.2.

Keep up the good work! We really appreciate what you are doing for the
community.

Kind regards,
Manfred.

Kind regards,

Manfred.

Manfred Lange
Read my blog On Agile Leadership http://agileleadership.blogspot.com - Like
my company on Facebook http://www.facebook.com/AgileUtilities - Follow me
on Twitter http://twitter.com/go4agile

On Fri, Nov 16, 2012 at 9:33 AM, Tom Marien notifications@github.comwrote:

Thanks for remarking this issue, fixed in version 1.0.5 ;) which is
released as of now

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/315#issuecomment-10424253.

[ManfredLange]

Just completed testing. It now works in our environments.

Thank you again for the fast response and fix.

Kind regards,
Manfred.

Kind regards,

Manfred.

Manfred Lange
Read my blog On Agile Leadership http://agileleadership.blogspot.com - Like
my company on Facebook http://www.facebook.com/AgileUtilities - Follow me
on Twitter http://twitter.com/go4agile

On Fri, Nov 16, 2012 at 12:37 PM, Manfred Lange manfredmlange@gmail.comwrote:

Hi Tom,

Fantastic! Thank you for the quick turnaround.

We'll test in the next couple of days and report back. Our tests cover
deployments on SQL Server 2012, SQL Azure and Postgres 9.2.

Keep up the good work! We really appreciate what you are doing for the
community.

Kind regards,
Manfred.

Kind regards,

Manfred.

Manfred Lange
Read my blog On Agile Leadership http://agileleadership.blogspot.com - Like
my company on Facebook http://www.facebook.com/AgileUtilities - Follow
me on Twitter http://twitter.com/go4agile

On Fri, Nov 16, 2012 at 9:33 AM, Tom Marien notifications@github.comwrote:

Thanks for remarking this issue, fixed in version 1.0.5 ;) which is
released as of now

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/315#issuecomment-10424253.

[tommarien]

Thanks for the feedback, it's good to have people like you, they give us the necessary feedback :)

[NanXiong]

Thank you guys! We are using 1.0.5.0 now and happy!