Add support for SQL Server schemas

[lprada]

Add support for SQL Server schemas

[dradovic]

As it stands, Mig# has no support for SQL Server schemas. This results in errors like #30. The reason it has no support is that Mig# is about having portable migrations. I will update the Manual to make this point very clear. So having something like Schemas["..."].Tables["..."]... in the fluent API is out of scope.

What should happen if somebody uses a dot when specifying a table name? In my opinion, a warning should be issued because I don't think that all DB platforms support that, if any.

Now, as you already went down that road to specify schema names in the table names, you can try and search the code (notoriously the SQL Server provider) for "dbo" and remove those instances that are not needed. Chances are, that things will just run then.

[lprada]

I like the Shemas syntax, what you xan do is that the provider handle the problem in the specific database, for example in sql server you use schemas and in a database that not supports it you transform the schema to a prefix or something

[dradovic]

Having different table names depending on the provider is not such a good idea as it would result in duplicated DAL code. For example, if you want to write an app that runs both on SQL Server and Oracle, you would have to do an "if Oracle then tableName = ...". Which brings me to the next question. How does ADO.NET handle this? Does ADO.NET have any support for SQLS schemas?

[lprada]

I understand what you say but that is a problem of the application and if the developer needs to use a Database that not support schemas he will know that schema is transform in a prefix, or maybe you can use a setting to enable prefix or not and problem solve, in the database that have schemas you use it and in the database that you dont you dont. If you use EF you dont have to worry about it, the provides handles the schemas.

Which brings me to the next question. How does ADO.NET handle this? Does ADO.NET have any support for SQLS schemas? I am using Entity Framework and I have no problem with schemas in SQL Server and Oracle databases, Before we use sql and we usa the dot notation.

[dradovic]

So does EF have explicit constructs to address schemas or do you simply using dots in table names?

[lprada]

In EF you can use Model First, in that case you drag and drop a table to VS and EF under the hook link that table to the entity, in that case the xml that is generate has the data of the schema so you dont have to do anything. In Code First you configurate the tables to Entitys and in this case you use the dot notation to asign the schema and table to a entity.

[lprada]

Please add this feature, If you need any help please let me know.

[dradovic]

I'm sorry but I currently don't have any need to implement this feature. If you want, I can do some paid development/consulting for you if you're working on a commercial project. I earn my living-hood as a freelancer.

Also, I wonder if other people need this feature. If so, please add your comment to this discussion.

[luizbon]

lprada, I'm working in a project that uses schemas on SQL Server and made a change to support. I'll not send a pull request yet as it is not well tested, but you can try it. The fork with this change is on https://github.com/luizbon/MigSharp/tree/AddSchemaSupport

[Kostassoid]

We too are using schemas in our environment, this is exceptionally useful in big projects with NH as ORM. This might also go well as an extension to modules, like providing schema name along with module name. Just an idea. @luizbon, thanks for sharing!

[luizbon]

I've not implemented like this because the modules determine what will run on migrations and not how it's run. The solution that I found was to implement the schema on providers that are responsible to generate the commands. I just uploaded more changes on the AddSchemaSupport branch.

[dradovic]

There's a good chance this will be tackled next.

[dradovic]

Available in 3.0.