Add user defined data type support for SQL Server SSDT

[MMagueta]

Proposed Changes

Currently, SSDTs for SQL Server do not support user defined data types. This means if a simple custom type is defined, such as the one from below, the column does not get translated to F#.

A simple UDDT:

CREATE TYPE LATITUDE FROM REAL;
GO
CREATE
    RULE RuleLatitudeRange
    AS
    @per >= -180.0 AND @per <= 180.0
GO
EXEC SP_bindrule 'RuleLatitudeRange', 'LATITUDE'
GO

From the lsp:

On the following table, where all the types are also simple overloads on REAL and INT:

CREATE TABLE dbo.TestUDDTs
(
    ID INT PRIMARY KEY,
    A MINUTES NOT NULL,
    B COMPASS NOT NULL,
    C LATITUDE NOT NULL,
    D LONGITUDE NOT NULL,
    E DAYS NOT NULL,
);

With this PR we now have:

A further explanation on the implementation may be found under Further comments

Types of changes

Changes this PR introduces:

• [ ] Bugfix (non-breaking change which fixes an issue)
• [X] New feature (non-breaking change which adds functionality)
• [ ] Breaking change (fix or feature that would cause existing functionality to not work as expected)

Checklist

• [ ] Build and tests pass locally
• [x] I have added tests that prove my fix is effective or that my feature works (if appropriate)
• [ ] I have added necessary documentation (if appropriate)

Further comments

• The solution is:
  • Parse the nodes in XML:

    <Element Type="SqlUserDefinedDataType" Name="[dbo].[MINUTES]">
    <Relationship Name="Schema">
    <Entry>
        <References ExternalSource="BuiltIns" Name="[dbo]" />
    </Entry>
    </Relationship>
    <Relationship Name="Type">
    <Entry>
        <References ExternalSource="BuiltIns" Name="[int]" />
    </Entry>
    </Relationship>
    </Element>

  • Generate a Map<UDDTName, UDDTInheritedType> (both algebraic aliases for string), where UDDTName is the given name to the type, and UDDTInheritedType is the base type that UDDTName inherits from
  • Add a new parameter to tryFindMappingOrVariant where such map is passed
  • Attempts to find the type in the normal typeMappingsByName map, if it does not find, attempts once again on the UDDT map, if something was found, searches for the base type in typeMappingsByName, otherwise defaults to SQL_VARIANT and consequently to System.Object
• Since UDDTs can only be referenced to simple primitives in SQL Server, I leverage the fact I don't have to worry on UDDTs being defined in terms of another UDDT.

[JordanMarr]

Nice. 👍

[MMagueta]

I have added a simple test, but I am having problems with running them locally on my machine. If someone can build them I would appreciate.

I tested using this script with a database I defined myself:

open FSharp.Data.Sql
open FSharp.Data.Sql.Common

let [<Literal>] vendor = DatabaseProviderTypes.MSSQLSERVER_SSDT
type Schema = SqlDataProvider<vendor, SsdtPath="/Users/mmagueta/Projects/FOSS/TestSQLProvider/base.dacpac">

type Config =
    { Host: string
      Port: string
      Database: string
      User: string
      Password: string }

[<EntryPoint>]
let main _ = 
    let config = {Host = "localhost"; Port = "1433"; Database = "msdb"; User = "sa"; Password = "P@ssw0rd"}
    let dataCtx =
        Schema.GetDataContext($"TrustServerCertificate = true; Server={config.Host},{config.Port}; Initial Catalog={config.Database}; User Id={config.User}; Password={config.Password}")
    let entity = dataCtx.Dbo.TestUddTs.Create()
    printfn "%A" <| ((entity.A.GetType().FullName) = "System.Int32")
    0

[MMagueta]

Anything I would still have to go through? If it's ok, I am done with the scope of this PR @JordanMarr

[JordanMarr]

I have submitted a PR to add my old SSDT unit test project back to the test sln. If @Thorium wants to add this back, I think it could be very useful way to add some test coverage around the SSDT provider, including your changes.

[Thorium]

Thanks!

[Thorium]

I'll release new version tomorrow.

[Thorium]

Since UDDTs can only be referenced to simple primitives in SQL Server

You can do user-defined table type like

CREATE TYPE [F#].[Payment] AS TABLE(
    [Date] [smalldatetime] NULL,
    [Amount] [money] NULL
)

Although I'm not sure if SQL is a good tool for this kind of domain modelling as it's not meant to be a general purpose programming language.

[MMagueta]

Since UDDTs can only be referenced to simple primitives in SQL Server

You can do user-defined table type like

CREATE TYPE [F#].[Payment] AS TABLE(
  [Date] [smalldatetime] NULL,
  [Amount] [money] NULL
)

Although I'm not sure if SQL is a good tool for this kind of domain modelling as it's not meant to be a general purpose programming language.

This is somewhat useful for hidden behavior inside procedures (to group a query and insert somewhere from it), triggers (like error handling to persist a rollback), etc; but not so much to expose it via the provider. That's why I parsed a user defined data type only, UDTTs are not even being parsed on the XML from the dacpac.