FluentMigrator Create Clob/Text

[Luiz Rolim]

What´s the right way to create a TEXT (MYSQL) or CLOB(ORACLE/SQLSERVER) using a Migration script? I mean something like: Alter.Column("column").OnTable("table").AsXXX

Also on: http://stackoverflow.com/questions/20999475/fluentmigrator-create-clob-text

[Andrew Busby]

Most likely it is Astring(4001)

--
You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to fluentmigrator-goog...@googlegroups.com.
To post to this group, send email to fluentmigrato...@googlegroups.com.
Visit this group at http://groups.google.com/group/fluentmigrator-google-group.
For more options, visit https://groups.google.com/groups/opt_out.

[Tony OHagan]

I recently faced this issue when migrating a SQL Server schema that used IMAGE, TEXT and NTEXT fields. 

I found two solutions depending on your requirements.

Firstly, if you're only using a single database provider you can always force a specific field type using: 

    Alter.Column("column").OnTable("table").AsCustom("TEXT").

Secondly, you can allow the type mapper for each DB provider to select a field type.

What the type mappers do is select a DB provider type based on DbType and field size.

For example, SQL Server 2000 maps DbType.String using one of the type map rules:

  https://github.com/schambers/fluentmigrator/blob/master/src/FluentMigrator.Runner/Generators/SqlServer/SqlServer2000TypeMap.cs

  

 internal class SqlServer2000TypeMap : TypeMapBase

{

  ...

  SetTypeMap(DbType.String, "NVARCHAR(255)");                           // 0
  SetTypeMap(DbType.String, "NVARCHAR($size)", UnicodeStringCapacity);  // UnicodeStringCapacity = 4000
  SetTypeMap(DbType.String, "NTEXT", UnicodeTextCapacity);              // UnicodeTextCapacity = 1073741823
  SetTypeMap(DbType.String, "NVARCHAR(MAX)", int.MaxValue);             // int.MaxValue == 2147483647
...
}

So SQL Server 2000 will emit NTEXT as the field type for this ...
      Alter.Column("column").OnTable("table").AsString(4001).  // > UnicodeStringCapacity and < UnicodeTextCapacity
 
Noet: In SQL Server 2005, IMAGE, TEXT and NTEXT fields are deprecated and replaced by NVARBINARY(MAX), VARCHAR(MAX) and NVARCHAR(MAX)
So these rules are overridden to generate the new types
https://github.com/schambers/fluentmigrator/blob/master/src/FluentMigrator.Runner/Generators/SqlServer/SqlServer2005TypeMap.cs

Did you spot the bug?  SQL Server 2000 will never emit an IMAGE field:

    SetTypeMap(DbType.Binary, "VARBINARY(8000)");                         // 0
    SetTypeMap(DbType.Binary, "VARBINARY($size)", AnsiStringCapacity);    // 8000 
    SetTypeMap(DbType.Binary, "VARBINARY(MAX)", int.MaxValue);            // 2147483647 BUG
    SetTypeMap(DbType.Binary, "IMAGE", ImageCapacity);                    // 2147483647 BUG