fsprojects / sqlprovider Goto Github PK

A general F# SQL database erasing type provider, supporting LINQ queries, schema exploration, individuals, CRUD operations and much more besides.

Home Page: https://fsprojects.github.io/SQLProvider

License: Other

Shell 0.05% F# 92.59% PLpgSQL 0.13% Batchfile 0.08% Dockerfile 0.03% TSQL 7.11%

fsharp sql-server typeprovider sqlite postgresql mysql firebirdsql mariadb oracle database

sqlprovider's Introduction

SQLProvider

A general .NET/Mono SQL database type provider. Current features :

LINQ queries
Lazy schema exploration
Automatic constraint navigation
Individuals
Transactional CRUD operations with identity support
Two-way data binding
Stored Procedures
Functions
Packages (Oracle)
Composable Query integration
Optional option types
Mapping to record types
Custom Operators
Supports Asynchronous Operations
Supports .NET Standard / .NET Core
Supports saving DB schema offline, and SQL-Server *.dacpac files

The provider currently has explicit implementations for the following database vendors :

SQL Server
SQLite
PostgreSQL
Oracle
MySQL (& MariaDB)
MsAccess
Firebird

There is also an ODBC provider that will let you connect to any ODBC source with limited features.

Documentation

SQLProvider home page contains the core documentation and samples. This documentation originates from docs/content/, so please feel free to submit a pull request if you fix typos or add additional samples and documentation!

Building

Mono: Run build.sh
Windows: Run build.cmd

Known issues

Database vendors other than SQL Server and Access use dynamic assembly loading. This may cause some security problems depending on your system's configuration and which version of the .NET framework you are using. If you encounter problems loading dynamic assemblies, they can likely be resolved by applying the following element into the configuration files of fsi.exe, devenv.exe and your program or the program using your library : http://msdn.microsoft.com/en-us/library/dd409252(VS.100).aspx

Example

Maintainer(s)

The default maintainer account for projects under "fsprojects" is @fsprojectsgit - F# Community Project Incubation Space (repo management)

sqlprovider's People

Contributors

Stargazers

Watchers

Forkers

colinbull fsprojects-archive jamestryand carstenkoenig frassle simonhdickson jeroldhaas forki vasily-kirichenko jespera rachelblasucci stift fabiogaragiola plumhead turtlechalk greaterkudu jorism robertpi orient-man modulexcite psfblair jasonweiyi jamesgorman2 jamessdixon natelkins cloudroutine drasticactions bentayloruk cynic fornever garfeildma transformersprimeabcxyz coolya ummon bd3717eb y0j0 cedarlogic ptrelford tforkmann anonitperson fradav gitter-badger tarmil johndoekyrgyz chicker vilinski jdphenix thedarktrumpet ingted fpellet wezeku s952163 rflechner matlafqc leonqn o7g8 edouardm object nhowka afractal piaste anilmujagic siklosg gibranrosa bennylynch hoetz ryphes drivehappy jlogar yukibonji shanecharles beauvankirk johano giuliohome maybesacred talbottmike jlturner etschbeijer dsyme devanarch michal-duda daveemmerson mirannda misstickles giorgiog francotiveron andnagy olofblomqvist dieselmeister mcliment tylike cstpdk ma3yta rudihorn taagens pacohams chupakhin jhbertra alexan1 inecas

sqlprovider's Issues

Create a Travis CI build

Create account
Create build config for master branch (see #21)
Show build status in readme: https://travis-ci.org/fsprojects/SQLProvider
Verify build
Enable unit tests

Multithread issues

Currently I'm running into issues where if I have one shared context or a context per method I'm hitting exceptional code paths (mostly trying to add things to dictionaries with a key that already exists). I'm happy to try and help fix this but I want to know what the expected behavior should be. Should you be able to use one context in multiple threads safely, or should you create a new context per thread or should you create a new context per thread but create the context while holding onto a lock for the type provider.
A) Context is thread safe:

// in thread 1
query { for row in ctx.table1 do select row }
// in thread 2
query { for row in ctx.table2 do select row }

B) Context creation is thread safe:

// in thread 1
let ctx = sql.GetDataContext()
query { for row in ctx.table1 do select row }
// in thread 2
let ctx = sql.GetDataContext()
query { for row in ctx.table2 do select row }

C) No thread safety

// in thread 1
let ctx = lock sql (fun () -> sql.GetDataContext())
query { for row in ctx.table1 do select row }
// in thread 2
let ctx = lock sql (fun () -> sql.GetDataContext())
query { for row in ctx.table2 do select row }

Nicer naming for DB objects.

I have had a few requests from people I know that are using it (as well as FSharpComposableQuery support (issue #48 and here) , about cleaning up the naming. So things like backticks and [] are minimized, basically so it looks like normal properties. Additionally, this may also include removing _ and case names properly.

On an implementation note, this doesn't look to bad as we can retain the DB name in a type that represents the DB object name.

MySQL connection setup

This is a question, not an issue -- what's the connection string & full setup for MySQL?

A+

GetDataContext always using first database connection

I'm trying to query 5 different SQL Server database instances that have the same database schema. Unfortunately, GetDataConect is returning the data from the first database that was connected to even though I'm passing in a different connection string.

type MyDb = SqlDataProvider<devEnvConnString, Common.DatabaseProviderTypes.MSSQLSERVER>
for db in dbs do
  let dc = MyDb.GetDataContext db.ConnectionString
  // do a query and process some data

I'm using the latest version 0.0.6-alpha from NuGet.

Missing Solution File

Instructions to build include "Open solution file. Build." But you haven't committed the solution file. (Not that it is hard to reconstruct.)

Oracle: Should allow users to have a different user between runtime and design time

A common enterprise scenario, is having the same schema under different users to separate environments, for example development might be HR_DEV and test might be HR_TEST currently the schema owner is statically compiled, which prevents users with this configuration using the provider.

This is quite a big change on a par with #87. In fact probably touches most of the same code.

Cannot find table and table columns for SQLite database

The code is like this:

open System
open System.Linq
open FSharp.Data.Sql
open System.Data.SQLite
open System.Data.SQLite.Generic
open Microsoft.FSharp.Linq

[<Literal>]
let connString = @"Data Source=d:\data\fmarket.sqlite;Version=3"

[<Literal>]
let lib = @"D:\Programming\Projects\FSQLite\packages\System.Data.SQLite.x86.1.0.90.0\lib\net451\"

type sql = SqlDataProvider<connString, Common.DatabaseProviderTypes.SQLITE, lib>

let data = sql.GetDataContext()

let rows = 
    query {
        for c in data.``[main].[SCompany]`` do
        select c
    }

Initially, there's no reported error before I compile the code. The Intellisense also works because it can find the table names. However, when I compile, an error occurs:

error FS3021: Unexpected exception from provided type 'FSharp.Data.Sql.SqlDataProvider,ConnectionString="Data Source=d:\\data\\fmarket.sqlite;Version=3",DatabaseVendor="1",ResolutionPath="D:\\Programming\\Projects\\FSQLite\\packages\\System.Data.SQLite.x86.1.0.90.0\\lib\\net451\\"+SqlService+[main].[SCompany]Entity' member 'GetMethods': The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: The given key was not present in the dictionary.

It means that the table cannot be found in the database. Further, if I get the first record in this table:

let rows = 
    query {
        for c in data.``[main].[SCompany]`` do
        select c
    }
    |> Seq.toArray

let firstRow = rows.First()

I cannot find the columns for firstRow which should be listed as members.

Finally, the code cannot compile.

In contrast, everything works for SQL server when the database with exactly the same data is switched to SQL Server and others remain unchanged.

sqlite - generated entities have no properties

When I open an sqlite db, for example, this works:

let track = ctx.``[main].[Track]``.Individuals.``As Name``.``cae05826-7d61-4b51-bcc1-1e12b9229529, ps``

But I have no access to the properties of this entity

track.Name // this is not generated

I can only do

track.GetColumn("Name")

Is this normal?

[question] Relative path to sqlite-db

I'm using the sqlite variant of SQLProvider:

let dbPath = "some-absolute-path-to\sqlite.db"

type dbContext = 
  SqlDataProvider<
    ConnectionString = dbPath, 
    DatabaseVendor = Common.DatabaseProviderTypes.SQLITE,
    UseOptionTypes   = false>

I would like it to be possible to at design-time make the path to the sqlite db be relative to something like the project in which the type-provider is used, but I can't seem to figure out how to do that?

MySQL should support CRUD ops

CRUD mechanics are in, but they need to be implemented in the MySQL provider implementation. This should be relatively simple, just steal the code from the SQL server version and tweak the SQL so that it is MySQL compliant

UseOptionTypes=true causes generate writable properties of type 'a option option

The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error in the context of provided type 'FSharp.Data.Sql.SqlDataProvider,ConnectionString="Data Source=test_server; Initial Catalog=TEST; Integrated Security=True",UseOptionTypes="True"+dataContext+[dbo].[test]Entity', member 'set_NAME'. The error: Type mismatch when building 'args': invalid parameter for a method or indexer property. Expected 'Microsoft.FSharp.Core.FSharpOption`1[Microsoft.FSharp.Core.FSharpOption`1[System.String]]', but received type 'Microsoft.FSharp.Core.FSharpOption`1[System.String]'.
Parameter name: receivedType    L:\stash\...\Source\Tests\DB.fs 34  5   Tests

I expect the type of set_NAME to be string option instead of string option option.

Will this provider work with FSharp.Linq.Experimental.ComposableQuery?

I assume no because it's erased instead of generated, but maybe there can be a way to make it work?
http://fsprojects.github.io/FSharp.Linq.Experimental.ComposableQuery/

PostgreSQL should support CRUD ops

CRUD mechanics are in, but they need to be implemented in the PostgreSQL provider implementation. This should be relatively simple, just steal the code from the SQL server version and tweak the SQL so that it is PostgreSQL compliant

Allow to specify company names (table prefix) as static type provider parameter

This is maybe a very special request, but Dynamics NAV stores the company name as table prefix. I would like to specify this as a static parameter in the type provider and get rid of this.

(And of course I would like to switch companies at runtime)

Support for 'varargs'/'params'

Using a recent version of SQLProvider (43fe50f) I get errors when trying to construct a query similar to the below:

query {
  for row in db.``[main].[sometable]`` do
  select row.NAME.TrimEnd()

The error I get is:

System.ArgumentException : Expression of type 'System.Char[][]' cannot be used for parameter of type 'System.Char[]' of method 'System.String TrimEnd(Char[])'

It occurs in line 148 of SqlRuntime.QueryExpression (https://github.com/fsprojects/SQLProvider/blob/master/src/SQLProvider/SqlRuntime.QueryExpression.fs#L148)

I think this might be related to the TrimEnd method with signature:

public string TrimEnd(
    params char[] trimChars
)

I.e. the method actually takes a variable number of arguments.

Connection to SQLite does not work

I try to use a simple SQLite v3 database but the type provider does not work.

open System
open System.Linq
open FSharp.Data.Sql
open System.Data.SQLite
open System.Data.SQLite.Generic

let [<Literal>] connString = @"Data Source=d:\data\fmarket.sqlite;Version=3"
let [<Literal>] lib = @"D:\Programming\Projects\FSQLite\packages\System.Data.SQLite.1.0.90.0\lib\net451\"

type sql = SqlDataProvider<connString,Common.DatabaseProviderTypes.SQLITE,lib>
let context = sql.GetDataContext()

The code compiles with no error but 2 errors always appear:
Error 1 The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Exception has been thrown by the target of an invocation. D:\Programming\Projects\FSQLite\FSQLite\Program.fs 12 12 FSQLite

Error 2 The field, constructor or member 'GetDataContext' is not defined D:\Programming\Projects\FSQLite\FSQLite\Program.fs 13 19 FSQLite

At first, the code simply does not compile and I found I need to copy the SQLite.Interop.dll from x86/x64 folder to the program folder. However, it still does not work even as the code compiles. I cannot get a data context that provides any strong types in it. Since the database it quite simple, it works whenever I use SQLite official library to query it or somewhere else in R via RSQLite package.

What may be the problem?

Add build/documentation infrastructure

I created the whole FAKE, nuget, FSharp.Formatting
infrastructure for the Dynamics NAV type provider. If you are interested I could do the same here.

distinct broken and groupBy not implemented

Distinct is broken due to the query always selecting all fields even when just one is needed, e.g.

query {
    for run in ctx.``[mpd_ti2].[Run]`` do 
    select (run.rProject)  
}

generates the following query

SELECT `run`.`iID` as `iID`,`run`.`rName` as `rName`,`run`.`rState` as `rState`,`run`.`rProject` as `rProject`,`run`.`rRatingCalc` as `rRatingCalc`,`run`.`rRatingUser` as `rRatingUser`,`run`.`rComment` as `rComment`,`run`.`rContacts` as `rContacts`,`run`.`sTimeIndexed` as `sTimeIndexed`,`run`.`sTimeCreated` as `sTimeCreated` FROM `mpd_ti2`.`Run` as `run`

while I would expect something like.

SELECT `run`.`rProject` as `rProject` FROM `mpd_ti2`.`Run` as `run`

When using distinct this causes major issues as it's made distinct over all fields instead of just the one your selecting. If I just want the list of distinct projects I would expect the following to work,

query {
    for run in ctx.``[mpd_ti2].[Run]`` do      
    select (run.rProject)  
    distinct
}

And while it generates a SELECT DISTINCT query it's over all fields again,

SELECT DISTINCT `run`.`iID` as `iID`,`run`.`rName` as `rName`,`run`.`rState` as `rState`,`run`.`rProject` as `rProject`,`run`.`rRatingCalc` as `rRatingCalc`,`run`.`rRatingUser` as `rRatingUser`,`run`.`rComment` as `rComment`,`run`.`rContacts` as `rContacts`,`run`.`sTimeIndexed` as `sTimeIndexed`,`run`.`sTimeCreated` as `sTimeCreated` FROM `mpd_ti2`.`Run` as `run`

The following query is what I would expect to be generated.

SELECT DISTINCT `run`.`rProject` as `rProject` FROM `mpd_ti2`.`Run` as `run`

As distinct is just short hand for grouping I tried using groupBy,

query {
    for run in ctx.``[mpd_ti2].[Run]`` do 
    groupBy (run.rProject) into projects
    select (projects.Key)
}

but this isn't yet implemented (groupBy throws an "unrecognised method call" exception).

Workaround is to pipe my results through Seq.distinct, but I'd prefer to get the database server to do that for me. Selecting only needed fields would also save on bandwidth and memory when generating intermediate tables for complex queries.

Oracle problem

When trying to connect to Oracle using:
type sql = SqlDataProvider< connectionString, Common.DatabaseProviderTypes.ORACLE, resolutionPath >
I get the error:
Tutorial.fsx(31,17): error FS3033: The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: An item with the same key has already been added.

The resolution path in my code points to the path ..odp.net\managed\common
where the dll Oracle.ManagedDataAccess.dll is located.

Create integration tests

select a database provider that is free and works on mono
create a SQL script that creates a test db
run the SQL script from the FAKE build
create a test project that uses the test db
compile and run the test project from the FAKE build

Problem connecting to SQLite on x64

I have the following code in a .fs file:

let con = @"Data Source=some-path-to\test.db; Version=3"
let res = @"C:\another-path-to-x64ver\contrib\sqlite\lib\net45"

type sql = SqlDataProvider<ConnectionString = con,
                                   DatabaseVendor = Common.DatabaseProviderTypes.SQLITE, 
                                   ResolutionPath = res,
                                   UseOptionTypes = true>

The error I get in Visual Studio 2012 is :

The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Could not load file or assembly 'file:...contrib\System.Data.SQLite.x64.1.0.89.0\lib\net45\System.Data.SQLite.dll' or one of its dependencies. An attempt was made to load a program with an incorrect format.

After a bit of searching around I saw this: http://pinksquirrellabs.com/post/2013/12/15/SqlProvider-SQLite-support-%28experimental%29.aspx

I have applied the suggested loadFromRemoteSources but that did not help. What does help though is to point to a x86-sqlite dll.

Is it (should it be) possible to use the SqlProvider on a x64-platform?

Support "count" in queries

This works:

query{ for o in db.Object do
       select o.Date } 
  |> Seq.length

this

query{ for o in db.Object do
       count }

brings:

System.Exception: Unuspported execution expression
   at Microsoft.FSharp.Linq.QueryModule.CallGenericStaticMethod@337.Invoke(Tuple`2 tupledArg)
   at Microsoft.FSharp.Linq.QueryModule.clo@1741-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Execute[a,b](FSharpExpr`1 )
   at <StartupCode$FSI_0006>.$FSI_0006.main@()

The world needs more type providers

Instead of making the database provider a static parameter of a god type provider consider to create specialized type providers for every database vendor.

This would make it easier to match optional parameters for the concrete provider (at least for the Dynamics NAV provider)

"Specified cast is not valid" with ODBC

I'm using SQLProvider 0.0.9-alpha (which claims to offer ODBC connectivity), I'm getting: "The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Specified cast is not valid."

when I do:

open FSharp.Data.Sql 
[<Literal>] 
let excelCs = @"DSN=foo" 
type xl = SqlDataProvider<ConnectionString = excelCs, DatabaseVendor = Common.DatabaseProviderTypes.ODBC>

foo: a User DSN in Data Sources (ODBC)

foo's driver: SQL Server Native Client 11.0

database: SQL Server 2008 R2 Express

Basic extension methods do not work for SQLite x86

open System
open System.Linq
open FSharp.Data.Sql
open System.Data.SQLite
open System.Data.SQLite.Generic
open Microsoft.FSharp.Linq

[<Literal>]
let connString = @"Data Source=d:\data\fmarket.sqlite;Version=3"

[<Literal>]
let lib = @"D:\Programming\Projects\FSQLite\packages\System.Data.SQLite.x86.1.0.90.0\lib\net451\"

type sql = SqlDataProvider<connString, Common.DatabaseProviderTypes.SQLITE, lib>

let context = sql.GetDataContext()

let row1 = context.``[main].[SCompany]``.First()

The context is correctly loaded but I can't use any of the extension methods like First() to the table.

The error is:
error FS3021: Unexpected exception from provided type 'FSharp.Data.Sql.SqlDataProvider,ConnectionString="Data Source=d:\data\fmarket.sqlite;Version=3",DatabaseVendor="1",ResolutionPath="D:\Programming\Projects\FSQLite\packages\System.Data.SQLite.x86.1.0.90.0\lib\net451"+SqlService+[main].[SCompany]Entity' member 'GetMethods': The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: The given key was not present in the dictionary.

The database absolutely works because I use it through RSQLIte in R with no problems. How do we know what happens here?

Connection to SQL Server does not work

When I create a SqlDataProvider with my SQL Server file database, errors always occur that "An exception of type 'System.Exception' occurred in FSharp.Data.SqlProvider.dll but was not handled in user code" and "Additional information: Unsupported expression. Ensure all server-side objects appear on the left hand side of predicates. The In and Not In operators only support the inline array syntax."

open System
open System.Linq
open FSharp.Data.Sql
open System.Data.SQLite
open System.Data.SQLite.Generic
let [<Literal>] connString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\Data\csmar\CSMAR.mdf;Integrated Security=True"
type sql = SqlDataProvider<connString,Common.DatabaseProviderTypes.MSSQLSERVER>
let data = sql.GetDataContext()
let ticks1 =
    query {
        for tick in data.``[dbo].[Tick]`` do
        where (tick.Stkcd.Equals("000001"))
        select (tick.Trddt,tick.Adjprcwd)
    }
let result = ticks1 |> Seq.toArray

In the code, the database file contains a very simple database where table Tick is needed. The error always occurs at

let data = sql.GetDataContext()

However, the code compiles with no error.
What may be the problem?

MSSQL issue with SByte

type SQL = SqlDataProvider<...>
let sql = SQL.GetDataContext()

let source = wl.``[dbo].[Source]``.Create(1y, false, false, false, 1y, 2y, false, 0y)
source.Name <- "Local folder"
source.Address <- "d:\FTP\TestUser1"
source.Priority <- 1y
source.Status <- 0y
wl.SubmitUpdates()

The code has no compilation errors.
Result:

System.ArgumentException: The parameter data type of SByte is invalid.
   at System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed)
   at System.Data.SqlClient.SqlParameter.GetMetaTypeOnly()
   at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
   at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at <StartupCode$FSharp-Data-SqlProvider>.$Providers.MsSqlServer.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates@504-2.Invoke(SqlEntity e) in F:\GIT\SQLProvider\src\SQLProvider\Providers.MsSqlServer.fs:line 509
   at Microsoft.FSharp.Primitives.Basics.List.iter[T](FSharpFunc`2 f, FSharpList`1 x)
   at FSharp.Data.Sql.Providers.MSSqlServerProvider.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates(IDbConnection con, FSharpList`1 entities) in F:\GIT\SQLProvider\src\SQLProvider\Providers.MsSqlServer.fs:line 503
   at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges() in F:\GIT\SQLProvider\src\SQLProvider\SqlRuntime.DataContext.fs:line 38
   at <StartupCode$FSI_0018>.$FSI_0018.main@()

Oracle should support CRUD ops

CRUD mechanics are in, but they need to be implemented in the Oracle provider implementation. This should be relatively simple, just steal the code from the SQL server version and tweak the SQL so that it is Oracle compliant

Everything is in upper case (MSSQL)

I built it from source and it generates everything in upper case now (the version that lays in NuGet generates normal names).

Is it intentional?

Integrate with deedle

type sql = SqlDataProvider<"Data Source=SFORKMANN;Initial Catalog=UWW;Integrated Security=True">

let ctx = sql.GetDataContext()


let debtData = 
    query {for x in ctx.``[dbo].[Test$USDebt]`` do
             select (x.Year,x.Debt) }
    |> Frame.ofRecords
    |> Frame.indexColsWith ["Year"; "Debt"]
    |> Frame.indexRowsInt "Year"
    |> Frame.dropSeries "Year"

It would be super duper awesome if we could find a way for Deedle to infer the column names. Frame.indexColsWith ["Year"; "Debt"] feels a bit strange.

// cc @tpetricek

PS: this type provider ROCKKKS!

SQLProvider does not support MS Access databases

Please develop support for Microsoft Access databases! Microsoft Office is a leading suite of applications enjoying widespread usage in businesses around the world, so a type provider supporting MS Access is in much demand.

Encapsultion of table names

type sql = SqlDataProvider<"Data Source=SFORKMANN;Initial Catalog=UWW;Integrated Security=True">

let ctx = sql.GetDataContext()

for line  in ctx.``[dbo].[Test$Gen_ Journal Line]`` do  
    printfn "%A" line.``Accounting Index``

brings

System.Data.SqlClient.SqlException (0x80131904): Falsche Syntax in der Nähe von 'Journal'.

which means "wrong syntax."

Table looks like:

CREATE TABLE [dbo].[Test$Gen_ Journal Line] ( ... )

PostgreSQL should support stored procedures

The current PostgreSQL provider does not implement the ISqlProvider.GetSprocs function.

The implementation should use the PostgreSQL metadata capabilities to establish the names and parameters of stored procedures. (Probably using INFORMATION_SCHEMA ... )

The more tricky bit is working out what columns the sproc returns. We can only really support simple procedures that don't return multiple or different recordsets depending on branching logic. OUT parameters are currently not supported so those ones should be ignored.

The SQL Server implementation (which is somewhat of a mess currently) executes the proc with some default parameters along with ``CommandBehaviour.SchemaOnly` to establish this information. I don't know the capabilities of PostgreSQL, perhaps there is a nicer way to achieve this.

Once you are able to return a list of sproc data, the rest should just all magically work!

query {} |> Seq.toList raises exception if the query returns empty seq

query {
    for x in db.``[dbo].[test]`` do
    select x
} |> Seq.toList

If [dbo].[test] is empty, then the following exception raises:

Parameter name: source
    at Microsoft.FSharp.Collections.SeqModule.Head[T](IEnumerable`1 source)
    at FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.convertExpression(SqlExp exp, List`1 entityIndex, IDbConnection con, ISqlProvider provider)
    at FSharp.Data.Sql.Runtime.QueryImplementation.executeQuery(ISqlDataContext dc, ISqlProvider provider, SqlExp sqlExp, List`1 ti)
    at FSharp.Data.Sql.Runtime.QueryImplementation.SqlQueryable`1.System-Collections-Generic-IEnumerable`1-GetEnumerator()
    at Microsoft.FSharp.Collections.SeqModule.ToList[T](IEnumerable`1 source)

Problems updating optional value types with SQLite

Suppose I use the SQLProvider with SQLite as follows (dateSinceEpoch is an optional integer column):

let email = ctx.``[main].[emails]``.Create()
email.dateSinceEpoch <- secondsSinceEpoch emailData.MailDate |> Some
email.sender <- emailData.MailSender |> Some
...
ctx.SubmitUpdates()

I get:

Error FS3033: The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an 
error in the context of provided type 
'FSharp.Data.Sql.SqlDataProvider,ConnectionString="Data Source=test.sqlitedb;
Version=3",DatabaseVendor="1",UseOptionTypes="True"+
dataContext+[main].[emails]Entity', member 'set_dateSinceEpoch'. 
The error: Invalid generic arguments�Parameter name: typeArguments (FS3033) (Persistence)

If I don't create the option type I first get this error:

Error FS0001: This expression was expected to have type�    Option<int64>    
�but here has type�    int64 (FS0001) (Persistence)

but the prior "Invalid generic" error also appears afterwards.

This issue appears to affect only cases of nullable columns that store value types.

When used in MonoDevelop creates many connections.

I've been trying to use this provider in MonoDevelop on Ubuntu to connect to a MySql database. The createTypes method is called repeatedly and the connections it creates are never closed until I end the whole MonoDevelop process. This quickly exhausts the connections available to the database.

MySQL should support stored procedures

The current MySQL provider does not implement the ISqlProvider.GetSprocs function.

The implementation should use the MySQL metadata capabilities to establish the names and parameters of stored procedures. (Probably using INFORMATION_SCHEMA, or maybe the Connection.GetSchema can tell you about sprocs)

The SQL Server implementation (which is somewhat of a mess currently) executes the proc with some default parameters along with ``CommandBehaviour.SchemaOnly` to establish this information. I don't know the capabilities of MySQL, perhaps there is a nicer way to achieve this.

Once you are able to return a list of sproc data, the rest should just all magically work!

MSSQL: properties corresponding to `tinyint` SQL type has `sbyte` F# type instead of `byte`

Version on NuGet do it right: tinyint -> byte (tinyint is unsigned byte in SQLServer).
The version in current master generates tinyint -> sbyte which causes the following exception in runtime:

System.ArgumentException : The parameter data type of SByte is invalid.
    at System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed)
    at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
    at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
    at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteScalar()
    L:\github\SQLProvider\src\SQLProvider\Providers.MsSqlServer.fs(627,0): at <StartupCode$FSharp-Data-SqlProvider>.$Providers.MsSqlServer.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates@622-2.Invoke(SqlEntity e)
    at Microsoft.FSharp.Primitives.Basics.List.iter[T](FSharpFunc`2 f, FSharpList`1 x)
    L:\github\SQLProvider\src\SQLProvider\Providers.MsSqlServer.fs(621,0): at FSharp.Data.Sql.Providers.MSSqlServerProvider.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates(IDbConnection con, FSharpList`1 entities)
    L:\github\SQLProvider\src\SQLProvider\SqlRuntime.DataContext.fs(53,0): at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges()

Oracle view with primary key constraint breaks typegen

In oracle views can specify constraints, although it is not enforced (disabled). If a view has primary key constraint, the type provider throws InvalidCastException : Unable to cast object of type 'System.DBNull' to type 'System.String'.
It looks like it tries to find the index for the primary key.

member __.CreateTypeMappings(con) = 
            if con.State <> ConnectionState.Open then con.Open()
            getSchema "DataTypes" [||] con
            |> createTypeMappings

            let indexColumns = 
                getSchema "IndexColumns" [|owner|] con
                |> DataTable.map (fun row -> row.[1] :?> string, row.[4] :?> string)
                |> Map.ofList

            getSchema "PrimaryKeys" [|owner|] con
            |> DataTable.cache primaryKeyCache (fun row ->
                let indexName = unbox row.[15]
                let tableName = unbox row.[2]
                match Map.tryFind indexName indexColumns with
                | Some(column) -> 
                    let pk = { Name = unbox row.[1]; Table = tableName; Column = column; IndexName = indexName }
                    Some(tableName, pk)
                | None -> None) |> ignore
            con.Close()

The exception is thrown in line let indexName=...

Query format

I've connected to a MySQL database called "api". Now I've found that queries work if I reference it as

query { for u in db.``[api].[users]`` ... }

-- even if I supplied Database=api; as a part of the connection string. I cannot simply say db.users. This seems to deviate from the examples at

http://msdn.microsoft.com/en-us/library/hh225374.aspx

Also, I cannot do things like

query { for u in db.``[api].[users]`` do sortBy user_id; select u }

-- I have to explicitly refer to the column in select, such as

query { for u in db.``[api].[users]`` do sortBy user_id; select (user_id, status) }

I guess this is the peculiarity of LINQ which Expert F# mentions. I'm new to the whole .net/LINQ scene, so I wonder what are those "entity" things I'm getting back? E.g., if I select the whole row, select u, I then can refer to columns like x.user_id. How do I know which columns are there? Does the MSDN link above fully apply to what we're getting from SQLProvider?

sortBy doesn't work if it's on its own

sortBy / sortByDescending does not work if it is the only expression in the query eg

query { for x in y do
        sortByDescending x.z
        head }

This is likely to with with alias / tuple things that happen in ast shakedown - or more specifically - have not happened due to the lack of other expressions. This will either be really simple to fix or a right pain, probably the latter :)

Create release.cmd

I assume you don't want to upload the nuget packages manually.
Here is what I do:

Create a release.cmd file in the project root

@echo off
cls
if not exist packages\FAKE\tools\Fake.exe (
.nuget\nuget.exe install FAKE -OutputDirectory packages -ExcludeVersion -Prerelease
)
packages\FAKE\tools\FAKE.exe build.fsx Release "NugetKey=MYNUGETKEY"
pause
Replace MYNUGETKEY with your nuget key.
Make sure the file is in .gitignore

With this helper you only have to modify the RELEASE_NOTES.md file.

/cc @pblasucci - maybe we find a way to put this into the scaffolding.

thenBy on optional column blows up

The query expression:

    query { 
        for calendarEntry in dataContext.``[main].[calendar_entries]`` do
        where (calendarEntry.timestamp.Value >= todayTimestamp)
        sortBy calendarEntry.timestamp
        thenBy calendarEntry.title
        select calendarEntry  } |> Seq.toList

Note that both timestamp and title are optional columns.

The result:

Unhandled Exception: System.Exception: CreateQuery, e = value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlOrderedQueryable`1[FSharp.Data.Sql.Common.SqlEntity]).ThenBy(calendarEntry => calendarEntry.GetColumnOption("title"))
at [email protected] (System.String message) [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.PrintfImpl+StringPrintfEnv`1[System.Linq.IQueryable].Finalize () [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.PrintfImpl+Final1@224[Microsoft.FSharp.Core.Unit,System.String,System.Linq.IQueryable,System.Linq.Expressions.Expression].Invoke (Microsoft.FSharp.Core.FSharpFunc`2 env, System.Linq.Expressions.Expression a) [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.OptimizedClosures+Invoke@3028[System.Linq.Expressions.Expression,System.Linq.IQueryable,Microsoft.FSharp.Core.FSharpFunc`2[Microsoft.FSharp.Core.Unit,Microsoft.FSharp.Core.PrintfImpl+PrintfEnv`3[Microsoft.FSharp.Core.Unit,System.String,System.Linq.IQueryable]]].Invoke (System.Linq.Expressions.Expression u) [0x00000] in <filename unknown>:0 
at FSharp.Data.Sql.Runtime.QueryImplementation+-cctor@111.System-Linq-IQueryProvider-CreateQuery (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
at System.Linq.Queryable.ThenBy[SqlEntity,FSharpOption`1] (IOrderedQueryable`1 source, System.Linq.Expressions.Expression`1 keySelector) [0x00000] in <filename unknown>:0 
at (wrapper dynamic-method) object:lambda_method (System.Runtime.CompilerServices.Closure,Microsoft.FSharp.Core.Unit)
at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&)
at System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00000] in <filename unknown>:0 
[ERROR] FATAL UNHANDLED EXCEPTION: System.Exception: CreateQuery, e = value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlOrderedQueryable`1[FSharp.Data.Sql.Common.SqlEntity]).ThenBy(calendarEntry => calendarEntry.GetColumnOption("title"))
at [email protected] (System.String message) [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.PrintfImpl+StringPrintfEnv`1[System.Linq.IQueryable].Finalize () [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.PrintfImpl+Final1@224[Microsoft.FSharp.Core.Unit,System.String,System.Linq.IQueryable,System.Linq.Expressions.Expression].Invoke (Microsoft.FSharp.Core.FSharpFunc`2 env, System.Linq.Expressions.Expression a) [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Core.OptimizedClosures+Invoke@3028[System.Linq.Expressions.Expression,System.Linq.IQueryable,Microsoft.FSharp.Core.FSharpFunc`2[Microsoft.FSharp.Core.Unit,Microsoft.FSharp.Core.PrintfImpl+PrintfEnv`3[Microsoft.FSharp.Core.Unit,System.String,System.Linq.IQueryable]]].Invoke (System.Linq.Expressions.Expression u) [0x00000] in <filename unknown>:0 
at FSharp.Data.Sql.Runtime.QueryImplementation+-cctor@111.System-Linq-IQueryProvider-CreateQuery (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
at System.Linq.Queryable.ThenBy[SqlEntity,FSharpOption`1] (IOrderedQueryable`1 source, System.Linq.Expressions.Expression`1 keySelector) [0x00000] in <filename unknown>:0 
at (wrapper dynamic-method) object:lambda_method (System.Runtime.CompilerServices.Closure,Microsoft.FSharp.Core.Unit)
at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&)
at System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00000] in <filename unknown>:0

Is SQLite able to work on mobile devices?

We have a working mono build, but little testing with it.

Are we able to use SQLite with mobile builds? If not, what would be required to achieve this?

From Dave Thomas via twitter

You can enable query expressions on portable F# Core so if the TP worked it would open things for mobile ...

This means it may well be possible....

Unrestricted query blows up

With this SQLite table:

CREATE TABLE emails(
    id INTEGER PRIMARY KEY NOT NULL,
    date TEXT,
    timestamp INTEGER, 
    sender TEXT,
    intro TEXT
);

And this query:

let emails = query { for email in dataContext.``[main].[emails]`` do
                      select email  } |> Seq.toList

I get an "input sequence was empty" exception. I don't get the exception if I do this:

let emails = query { for email in dataContext.``[main].[emails]`` do
                      sortBy email.timestamp
                      select email  } |> Seq.toList

Here is the exception:

System.ArgumentException: The input sequence was empty.
Parameter name: source
at Microsoft.FSharp.Collections.SeqModule.Head[String] (IEnumerable`1 source) [0x00000] in <filename unknown>:0 
at FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.convertExpression (FSharp.Data.Sql.Common.SqlExp exp, System.Collections.Generic.List`1 entityIndex, IDbConnection con, ISqlProvider provider) [0x00000] in <filename unknown>:0 
at FSharp.Data.Sql.Runtime.QueryImplementation.executeQuery (ISqlDataContext dc, ISqlProvider provider, FSharp.Data.Sql.Common.SqlExp sqlExp, System.Collections.Generic.List`1 ti) [0x00000] in <filename unknown>:0 
at FSharp.Data.Sql.Runtime.QueryImplementation+SqlQueryable`1[FSharp.Data.Sql.Common.SqlEntity].System-Collections-Generic-IEnumerable`1-GetEnumerator () [0x00000] in <filename unknown>:0 
at Microsoft.FSharp.Collections.SeqModule.ToList[SqlEntity] (IEnumerable`1 source) [0x00000] in <filename unknown>:0

Copy blog post to docs

In order to get a starting point for the docs I think it's ok to copy http://pinksquirrellabs.com/post/2013/12/09/The-Erasing-SQL-type-provider.aspx

Basic extension methods do not work for SQLite x86

open System
open System.Linq
open FSharp.Data.Sql
open System.Data.SQLite
open System.Data.SQLite.Generic
open Microsoft.FSharp.Linq

[<Literal>]
let connString = @"Data Source=d:\data\fmarket.sqlite;Version=3"

[<Literal>]
let lib = @"D:\Programming\Projects\FSQLite\packages\System.Data.SQLite.x86.1.0.90.0\lib\net451\"

type sql = SqlDataProvider<connString, Common.DatabaseProviderTypes.SQLITE, lib>

let context = sql.GetDataContext()

let row1 = context.``[main].[SCompany]``.First()

The context is correctly loaded but I can't use any of the extension methods like First() to the table.

The database absolutely works because I use it through RSQLIte in R with no problems. How do we know what happens here?

Consider showing how FSharpComposableQuery can be used with SQLProvider

The FSharpComposableQuery nuget package is an interesting approach to normalizing F# queries to allow them to be more compositional and safe.

It would be interesting and useful to check if this approach to queries can be used in conjunction with SQLProvider and assess whether it brings benefits. If so it could be documented in the tutorials here.

http://fsprojects.github.io/FSharp.Linq.Experimental.ComposableQuery/

@ixtreon and @jamescheney are the primary contributors to the query normalizer.

Basic extension methods do not work for SQLite x86

open System
open System.Linq
open FSharp.Data.Sql
open System.Data.SQLite
open System.Data.SQLite.Generic
open Microsoft.FSharp.Linq

[<Literal>]
let connString = @"Data Source=d:\data\fmarket.sqlite;Version=3"

[<Literal>]
let lib = @"D:\Programming\Projects\FSQLite\packages\System.Data.SQLite.x86.1.0.90.0\lib\net451\"

type sql = SqlDataProvider<connString, Common.DatabaseProviderTypes.SQLITE, lib>

let context = sql.GetDataContext()

let row1 = context.``[main].[SCompany]``.First()

The context is correctly loaded but I can't use any of the extension methods like First() to the table.

The database absolutely works because I use it through RSQLIte in R with no problems. How do we know what happens here?

fsprojects / sqlprovider Goto Github PK

sqlprovider's Introduction

SQLProvider

Documentation

Building

Known issues

Example

Maintainer(s)

sqlprovider's People

Contributors

Stargazers

Watchers

Forkers

sqlprovider's Issues

Recommend Projects

Recommend Topics

Recommend Org