SQL Type Provider

Ross McKinlay, January 2014

@pezi_pink

www.pinksquirrellabs.com

github.com/pezipink/sqlprovider

But….. We already have a SQL type provider!!!

• Only supports SQL Server

• Uses SQLMetal.exe to generate code

• Is not really an erasing type provider, doesn’t scale

• Is closed source

• Has very limited extensibility points

• Supports almost all LINQ functions

• Limited schema exploration mechanics

• Supports any number of database vendors

• No code generation step

• Fully erasing type provider, can scale to massive schemas

• Completely open source!

• Pluggable back end, and open source. Go WILD!

• Supports a limited set of LINQ functions (currently)

• Constraint navigation and querying, “dot individuals”

SqlProvider Features• Instant access to a variety of database vendors with no code generation step

• Lazily evaluated metadata enabling scaling to large schemas

• LINQ queries, presently with the following supported keywords : – select, select many(for), join, distinct, where, take (top), exactlyOne (single), sortBy, sortByDescending, thenBy, thenByDescending

• “Dot Individuals” allowing you to directly view the rows of the database as static types via intelliense

• Schema navigation – you can directly enumerate over the constraints of an entity – no need to understand the relational design of the database up front to perform joins

• Custom operators suc as in In |=| and Like =%

• F# Option types can be used for nullable columns

• Stored procedures (well, sort of, support for this isn’t great yet!)

• Currently support for MS SQL Server SQLite, Postgresql

• Debugging hooks

Compile Time Execution Time

LINQ

Type Provider

SQL Entity

ISqlProviderSchema Metadata

Provided TypesQuotations

DataContext

Schema Metadata

EmployeeId, Name, etc

CompanyId, Address, etc

OrderId, Amount, etc

Entity Attributes (Columns)

Provided Types

SqlEntityKeyValue Pairs

Erased Type

Schema Metadata

Employee{ FK Company –> Employee }

Company{ FK Company –> Employee }

{ FK Company -> Order }

Order{FK Company -> Order }

Entity Relationships (Constraints)

Provided Types

StaticDataContext

Constraint information

Am I a compiler? Yes. Pretty much.

LINQ Expression

LINQ AST

SQL Query Type Optimise

Generate SQL

Execute Query

Project Results

SQL AST

RUNTIME

ISqlProvider

LINQ Expression Trees!

Nested Active Patterns! | MethodCall(None, (MethodWithName "Join"), [createRelated Convert(MethodCall(_, (MethodWithName "_CreateEntities"), [String destEntity] )) OptionalQuote (Lambda([ParamName sourceAlias],SqlColumnGet(sourceTi,sourceKey,_))) OptionalQuote (Lambda([ParamName destAlias],SqlColumnGet(destTi,destKey,_))) OptionalQuote (Lambda(projectionParams,_))]) ->

| OptionalOuterJoin(outerJoin,MethodCall(None,(MethodWithName "_CreateRelated"),

[param; _; String PE; String PK; String FE; String FK; String IE; RelDirection dir;])) ->

| MethodCall(Some(ParamName name | PropertyGet(_,PropName name)), (MethodWithName "GetColumn"),[String key]) ->

ProjectionAnalysis and transformation steps are applied to the LINQ projection expression;

1) To extract fields that have been explicitly been selected so they can be used to construct a sensible query where possible

2) Entity access nodes are replaced with calls to GetSubEntity() which performs the magic allowing it to seem as if using an ORM, when in reality each row’s data is stored in one master SqlEntity

a.Name b.Addr1DataRow(SqlEntity) b.Addr2 a.Title c.Id c.Amount a.Phone b.Addr2

GetSubEntity(a) GetSubEntity(b) GetSubEntity(c)

SqlEntity A SqlEntity B SqlEntity C

Extensibility via ADO.NET

type internal ISqlProvider = abstract CreateConnection : string -> IDbConnection abstract CreateCommand : IDbConnection * string -> IDbCommand abstract CreateCommandParameter : string * obj * SqlDbType option -> IDataParameter abstract CreateTypeMappings : IDbConnection -> Unit abstract ClrToEnum : (string -> SqlDbType option) with get abstract SqlToEnum : (string -> SqlDbType option) with get abstract SqlToClr : (string -> Type option) with get abstract GetTables : IDbConnection -> Table list abstract GetColumns : IDbConnection * Table -> Column list abstract GetPrimaryKey : Table -> string option abstract GetRelationships : IDbConnection * Table -> (Relationship list * Relationship list) abstract GetSprocs : IDbConnection -> Sproc list abstract GenerateQueryText : SqlQuery * string * Table *

Dictionary<string,ResizeArray<string>> -> string * ResizeArray<IDataParameter>

All you need to do is implement most of this interface! WHAT COULD BE SIMPLER!

^ Of course, this last one is the kicker… but thankfully you can largely just copy and paste from an existing provider

Questions

¿