Support of data types other than the enumerated by DbType

[rstuven]

This would be useful to specify some built-in types (eg. GEOMETRY) or user defined types.

[dradovic]

I see. But as I said in #28, the reason Mig# uses the DbType enumeration is to be able to write cross-platform compatible migrations. The user should be able to run the same migration on Oracle and on SQL Server (for example). If you start using features that are specific to only one platform (like GEOMETRY or user defined types), the portability of your migrations is subverted.

If you still want to go down this road, you can always emit custom SQL to do whatever you want (as shown in the manual).

I'm a big fan of opinionated software and therefore I doubt that extending Mig#'s API in this direction is the right thing to do. What do you think?

[rstuven]

I understand your point, and cross-platform compatibiity is a concern for me too, but disagree with your conclusion.

Sticking to GEOMETRY: Spatial databases, or some support of spatial capabiities, are getting too common to be ignored. You can find an equivalent of GEOMETRY in almost every major provider. Just happens there's no DbType for it. Of course, I always can emit custom SQL, but I'm losing some advantages of using the fluent interface of Mig#. This is how I'm managing it at the moment:

        db.CreateTable("Trace")
            .WithPrimaryKeyColumn("Id", DbType.Int32).AsIdentity()
            .WithNotNullableColumn("Position", DbType.String)
            .WithNotNullableColumn("Timestamp", DbType.DateTime)
            ;

        if (db.Context.ProviderMetadata.Name == ProviderNames.SqlServer2008 || 
       db.Context.ProviderMetadata.Name == ProviderNames.PostgreSQL)
            db.Execute(
                "ALTER TABLE Trace DROP COLUMN Position;" +
                "ALTER TABLE Trace ADD Position GEOMETRY NOT NULL;");

        if (db.Context.ProviderMetadata.Name == ProviderNames.Oracle)
            db.Execute(
                "ALTER TABLE Trace DROP COLUMN Position;" +
                "ALTER TABLE Trace ADD Position SDO_GEOMETRY NOT NULL;");

I'm generating a string column for providers not supporting geometry natively. Then I regenerate the column for every specific case. The result is too verbose. Perhaps some refactoring could help, but it would be great if we could write something like this:

        db.CreateTable("Trace")
            .WithPrimaryKeyColumn("Id", DbType.Int32).AsIdentity()
            .WithNotNullableColumn("Position", DbType.String)
                .UseCustomType("GEOMETRY").In(ProviderNames.SqlServer2008, ProviderNames.PostgreSQL)
                .UseCustomType("SDO_GEOMETRY").In(ProviderNames.Oracle)
            .WithNotNullableColumn("Timestamp", DbType.DateTime)
            ;

[rstuven]

(BTW, "PostgreSQL" is there just for sake of the example)

[dradovic]

(I was already hoping that you had implemented a PostgreSQL provider ;)

How are you then loading/storing objects from/to the geometry column using ADO.NET?

[dradovic]

The reason why I'm asking this question is to find out if there is further duplication of data access code. If yes, it might be more pragmatic to simply use an X and a Y column which works on all database platforms the same way.