Version control instructions
Git information is in a separate document.
The environment
Databases that you should need for development:
- Demo-data database scripts are at: /src/DatabaseScripts/
- Access database is at: /docs/files/msaccess/Northwind.MDB
- SQLite database is at: /tests/SqlProvider.Tests/db/northwindEF.db
Even though our test run will run modifications to the test databases, don't check in these *.mdb
and *.db
files with your commit, to avoid bad merge-cases.
Solution structure
We use Fake and Paket. Before opening the solutions, you have to run build.cmd
on Windows (or sh ./build.sh
on Mac/Linux).
The main source solution is SQLProvider.sln
.
The unit tests are located in another one, SQLProvider.Tests.sln
, and when you open the solution, it will lock the bin\net48\FSharp.Data.SqlProvider.dll
, and after that you can't build the main solution.
- To debug design-time features, you "Attach to process" the main solution debugger to another instance of Visual Studio running the test solution.
- To debug runtime, attach it to e.g. fsi.exe and run the code in the interactive.
Workarounds for "file in use" (issue #172)
- Debugging execution: Have all the test-files closed in your test-project when you open it with VS. Then you can run tests from the Tests Explorer window (and even debug them if you open the files to that instance of VS from src\SqlProvider).
- Or you can open tests with some other editor than Visual Studio 2015
Referenced Files
- Documentation is located at SQLProvider/docs/content/core, and it's converted directly to
*.html
help files by the build-script. src/ProvidedTypes.fsi
,src/ProvidedTypes.fs
andsrc/Code/ExpressionOptimizer.fs
are coming from other repositories restored by the first build. The location is at packet.dependencies. Don't edit them manually.
Test cases
There are database-specific test files as scripts in the test solution, /tests/SqlProvider.Tests/scripts/, but also one generic /tests/SqlProvider.Tests/QueryTests.fs which is running all the SQLite tests in the build script.
High-level description of the provider
Context and design time
You have a source code like:
// type sql = SqlDataProvider<...params...>
let dc = sql.GetDataContext()
What will first happen in the design-time, is that this will call createTypes
of SqlDesignTime.fs and create (as lazily as possible) the database schema types (the shape of the database). These methods are added to the sql.datacontext
and are stored to concurrent dictionaries. Visual Studio will do a lot of background processing so thread-safety is important here.
GetDataContext()
will return a dynamic class called dataContext which will on design-time call class SqlDataContext in file SqlRuntime.DataContext.fs through interface ISqlDataContext
. SqlDataContext uses ProviderBuilder to create database specific providers, fairly well documented ISqlProvider
in file SqlRuntime.Common.fs.
Querying
The entity-items themselves are rows in the database data, and they are modelled as dynamic sub-classes of SqlEntity
, base-class in file SqlRuntime.Common.fs which can be thought of as a wrapper for Dictionary<string,obj>
(a column name, and the value). SqlEntity is actually used for all kinds of result data, so the data columns may not correspond to the actual data values. Mostly the results of the data are shaped as SqlQueryable<SqlEntity>
or SqlQueryable<'T>
, which is SQLProvider's class for IQueryable<'T>
items.
let qry =
query {
for cust in dc.Main.Customers do
where ("ALFKI" = cust.CustomerId)
select cust
} |> Seq.toArray
This query is translated to a LINQ-expression-tree through Microsoft.FSharp.Linq.QueryFSharpBuilder. That will call IQueryable<'T>
's member Provider to execute two things for the LINQ-expression-tree: first CreateQuery
and later Execute
.
Parsing the LINQ-expression-tree
CreateQuery
will hit our SqlQueryable<...>
's Provider (IQueryProvider) property. LINQ-expression-trees can be recursive type structures, so we will call CreateQuery for each linq-method. We get the expression-tree as a parameter and parse that with (multi-layer-) active patterns.
Our example of the LINQ-expression tree is:
|
so it would hit this in SqlRuntime.Linq.fs:
| MethodCall(None, (MethodWithName "Where" as meth), [ SourceWithQueryData source; OptionalQuote qual ]) ->
because the LINQ-expression-tree has ExpressionType.Call
named "Where" with source of IWithSqlService (which is the SqlQueryableCondition
). If the conditions have SqlColumnGet
s, a pattern that says that it's SqlEntity
with method GetColumn
, we know that it has to be part of SQL-clause.
We collect all the known patterns in IWithSqlService
s field SqlExpression, being a type SqlExp
, our non-complete known recursive model-tree of SQL clauses.
Execution of the query
Eventually, there also comes the call executeQuery
(or executeQueryScalar
for SQL-queries that will return a single value like count), either by enumeration of our IQueryable or at the end of LINQ-expression-tree. That will call QueryExpressionTransformer.convertExpression
. What happens there (in
SqlRuntime.Linq.fs):
- We create a projection-lambda. This is described in detail below.
- We convert our
SqlExp
to real SQL-clause withQueryExpressionTransformer.convertExpression
calling provider'sGenerateQueryText
-method. Each provider may have some differences in their SQL-syntax. - We gather the results as
IEnumerable<SqlEntity>
(or a single return value like count). - We execute the projection-lambda to the results.
In our example, the whole cust object was selected.
For security reasons, we don't do SELECT *
, but we actually list the columns at compile time.
The TupleIndex
of IWithSqlService is a way to collect joined tables to match the sql-aliasses, here the [cust]
.
|
Projection-lambda
Now, if the select-clause had been complex:
let qry2 =
query {
for emp in dc.Main.Employees do
select (emp.BirthDate.DayOfYear + 3)
} |> Seq.toArray
We don't know the function of DayOfYear for each different SQL-providers (Oracle/MSSQL/Odbc/...), but we still want this code to work. The LINQ-expression-tree for this query is:
|
What happens now is that in SqlRuntime.QueryExpression.fs, we parse the whole LINQ-expression-tree, and find the parts that we do know to belong to SQL:
the SqlEntity's emp.GetColumn("BirthDate")
, and create a lambda-expression where this is replaced with a parameter:
fun empBirthDate -> empBirthDate.DayOfYear + 3
Now when we get the empBirthDate from the SQL result, we can execute this lambda for the parameter in the .NET-side, not SQL, and then we get the correct result. This is done with for e in results -> projector.DynamicInvoke(e)
in SqlRuntime.Linq.fs.
How to debug SQLProvider in your own solution, not the test-project
The runtime debugging can be done just like any other:
- Build your own project
- Then just build SQLProvider.sln and go to bin-folder, select your framework, and copy FSharp.Data.SqlProvider.dll and FSharp.Data.SqlProvider.pdb
- Replace your own project's bin-folder copies of these two files, run your software without rebuilding.
Debugging the design-time VS2022 is doable but a bit more complex. This will mess up your SQLProvider Nuget cache, so after done, delete the SQLProvider cache-folder and restore the package again.
- Open SQLProvider.sln (with Visual Studio 2022) and build it (in debug mode). Keep this open for now.
- Open Explorer, it has made under bin-folder some folders, e.g. \lib and \typeproviders (and under them per framework like \net48 \net6.0 \netstandard2.0 \netstandard2.1)
- Open another explorer, go to your location of Nuget cache, the version you are using e.g.
C:\Users\me\.nuget\packages\sqlprovider\1.4.8
- Replace the Nuget cache \typeproviders folder with your fresh bin typeproviders folder.
- Replace the Nuget cache \lib folder with your fresh bin lib folder.
- Open another instance of VS2022 to the start-screen, but don't open any project yet.
- Go back to your first instance of VS2022 with SQLProvider.sln. Add some breakpoints. Select from the top menu: Debug - Attach to Process...
- Select devenv.exe, which is another VS2022 instance.
- Switch to this new instance and load your own project that uses SQLProvider, and it'll stop at the breakpoints.
Other things to know
- SQLProvider also runs ExpressionOptimizer functions to simplify the LINQ-expression-trees
-
If you do IN-query (LINQ-Contains) to IEnumerable, it's as normal IN-query. Still, if the source collection is SqlQueryable
, then the query is serialized as a nested query, where we have to check that the parameter names won't collide (i.e. param1 can be used only once). This documentation was written on 2017-04-11.
namespace FSharp
--------------------
namespace Microsoft.FSharp
namespace FSharp.Data
--------------------
namespace Microsoft.FSharp.Data
type LiteralAttribute = inherit Attribute new: unit -> LiteralAttribute
--------------------
new: unit -> LiteralAttribute
<summary>Typed representation of a database</summary> <param name='ConnectionString'>The connection string for the SQL database</param> <param name='ConnectionStringName'>The connection string name to select from a configuration file</param> <param name='DatabaseVendor'> The target database vendor</param> <param name='IndividualsAmount'>The amount of sample entities to project into the type system for each SQL entity type. Default 50. Note GDPR/PII regulations if using individuals with ContextSchemaPath.</param> <param name='UseOptionTypes'>If set, F# option types will be used in place of nullable database columns. If not, you will always receive the default value of the column's type even if it is null in the database.</param> <param name='ResolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types. Types used in desing-time: If no better clue, prefer .NET Standard 2.0 versions. Semicolon to separate multiple.</param> <param name='Owner'>Oracle: The owner of the schema for this provider to resolve. PostgreSQL: A list of schemas to resolve, separated by spaces, newlines, commas, or semicolons.</param> <param name='CaseSensitivityChange'>Should we do ToUpper or ToLower when generating table names?</param> <param name='TableNames'>Comma separated table names list to limit a number of tables in big instances. The names can have '%' sign to handle it as in the 'LIKE' query (Oracle and MSSQL Only)</param> <param name='ContextSchemaPath'>The location of the context schema previously saved with SaveContextSchema. When not empty, will be used to populate the database schema instead of retrieving it from then database.</param> <param name='OdbcQuote'>Odbc quote characters: Quote characters for the table and column names: `alias`, [alias]</param> <param name='SQLiteLibrary'>Use System.Data.SQLite or Mono.Data.SQLite or select automatically (SQLite only)</param> <param name='SsdtPath'>A path to an SSDT .dacpac file.'</param>
<summary> .NET Framework default </summary>
<summary>Returns an instance of the SQL Provider using the static parameters</summary>
(+0 other overloads)
SqlDataProvider<...>.GetDataContext(selectOperations: SelectOperations) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='selectOperations'>Execute select-clause operations in SQL database rather than .NET-side.</param>
(+0 other overloads)
SqlDataProvider<...>.GetDataContext(commandTimeout: int) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='commandTimeout'>SQL command timeout. Maximum time for single SQL-command in seconds.</param>
(+0 other overloads)
SqlDataProvider<...>.GetDataContext(transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>
(+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param>
(+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, selectOperations: SelectOperations) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='selectOperations'>Execute select-clause operations in SQL database rather than .NET-side.</param>
(+0 other overloads)
SqlDataProvider<...>.GetDataContext(transactionOptions: Transactions.TransactionOptions, commandTimeout: int) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param><param name='commandTimeout'>SQL command timeout. Maximum time for single SQL-command in seconds.</param>
(+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, commandTimeout: int) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='commandTimeout'>SQL command timeout. Maximum time for single SQL-command in seconds.</param>
(+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>
(+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, resolutionPath: string) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='resolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types. Types used in desing-time: If no better clue, prefer .NET Standard 2.0 versions. Semicolon to separate multiple.</param>
(+0 other overloads)
<summary> The table Customers belonging to schema main</summary>
<summary>CustomerID: nchar(5)</summary>
module Seq from FSharp.Data.Sql
--------------------
module Seq from Microsoft.FSharp.Collections
<summary> The table Employees belonging to schema main</summary>
<summary>BirthDate: datetime</summary>
<summary>Gets the day of the year represented by this instance.</summary>
<returns>The day of the year, expressed as a value between 1 and 366.</returns>