schambers/fluentmigrator — pull request #428 by stefan-ojog (2013-07-01 13:08:49+00:00)
Title: Added description support for tables and columns on MS Sql Server and Oracle

Description support for tables and columns fixes issues #291 and #368. Implemented this for Oracle and MS SqlServer (version 2005 or higher).

Solution summary:

- The CreateTableExpression and AlterTableExpression contain a new string property TableDescription
- The ColumnDefinition contains a new string property called ColumnDescription
- Created IDescriptionGenerator interface and implemented 3 classes for this contract: OracleDescriptionGenerator, SqlServer2005DescriptionGenerator and EmptyDescriptionGenerator
- The classes that generate the sql (such as OracleGenerator, SqlServer2005Generator) will use the description generator classes mentioned above for generating the appropriate scripts using newly overridden methods:
* Generate(CreateTableExpression expression)
* Generate(AlterTableExpression expression)
* Generate(CreateColumnExpression expression),
* Generate(AlterColumnExpression expression)

1. On Oracle the syntax for adding / updating description on tables and columns is:

[For table] COMMENT ON TABLE TestTable IS 'TestDescription'
[For column] COMMENT ON COLUMN TestColumn IS 'TestColumnDescription'

The OracleDescriptionGenerator generates sql statements as above, but I've modified OracleGenerator (see above the precise methods) as well, in order to generate scripts as follows (for example):

BEGIN EXECUTE IMMEDIATE 'CREATE TABLE TestTable1 (TestColumn1 NVARCHAR2(255) NOT NULL, TestColumn2 NUMBER(10,0) NOT NULL)';
EXECUTE IMMEDIATE 'COMMENT ON TABLE TestTable1 IS ''TestDescription''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN TestTable1.TestColumn1 IS ''TestColumn1Description'''; END;

Note: The statements needed to wrapped in a BEGIN-END block due to the fact that ExecuteNonQuery was failing when trying to execute multiple statements. Also, the EXECUTE IMMEDIATE are required in order to being able to execute DDL statements in BEGIN-END block.

2. On MS Sql Server (2005 or higher) descriptions are added on tables and columns using the following stored procedure:

[For table] EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TestDescription', @level0type=N'SCHEMA', @level0name='dbo', @level1type=N'TABLE', @level1name='TestTable'
[For column] EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'TestColumnDescription', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'Table', @level1name = 'TestTable', @level2type = N'Column', @level2name = 'TestColumn'

As with Oracle implementation, the SqlServerGenerator (the same methods as above) needed to be modified so that the generated sql looks like below:

CREATE TABLE [...]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TestDescription', @level0type=N'SCHEMA', @level0name='dbo', @level1type=N'TABLE', @level1name='TestTable1';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'TestColumn1Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level
1type = N'Table', @level1name = 'TestTable1', @level2type = N'Column', @level2name = 'TestColumn1'

Note: In case of AlterTable or AlterColumn, at the time of the migration the target tables / columns may already contain descriptions which would make the execution of the sp_addextendedproperty to throw an error and the migration to fail. For this, the script would look like this (if the property exists, then it is first deleted and subsequently added with the new value):

IF EXISTS ( SELECT * FROM fn_listextendedproperty(N'MS_Description', N'SCHEMA',N'dbo', N'TABLE', N'TestTable1', NULL, NULL))
EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA', @level0name='dbo', @level1type=N'TABLE', @level1name='TestTable1';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TestNewDescription', @level0type=N'SCHEMA', @level0name='dbo', level1type=N'TABLE', @level1name='TestTable1'

The rest of the generator classes haven't been modified, except for injecting the EmptyDescriptionGenerator which doesn't change the existing functionality.

Unit tests have been added for the 2 specialized implementations of IDescriptionGenerator (the ones explained above). Also, added more tests in OracleGeneratorTests and SqlServerGeneratorTests according to the new overridden methods.

In addition, I've also moved static FormatSqlEscape method (which was duplicated in multiple classes) in newly added static class FormatSqlHelper and refactored accordingly.

I haven't implemented description support for other database engines (as I only work with Oracle and MS SQL Server), but it could be implemented using this structure.