Hi

We are currently using plans everywhere and I have somehow (miss) understood that plan also wraps with transactions. However we have some code like this: if exists() then update() else insert(). This constantly generates duplicate primary key violations since data is tried to update while it's actually exists during rapid repetition of function.

Commands have clear way of connection and therefore transaction management but how transactions should be used with plans?

Is there a way to support dynamic queries?

i.e.

Given type Read = SQL<"""Select * from MyTable"""> and let query = ["Id", 5L; "Scope", "Demo"]

Is there a way to intercept Read and "add or update the where clause" instead of having to create a different type for each scenario - type ReadById, type ReadByScope, type ReadByIdAndScope?

I'm work a large number of fields and currently experimenting with how to make the queries with large optional fields a bit dynamic.

Any tips, tricks, workaround?

Following the tutorial in a .net 4.7.2 console app with FSharp.Core 4.5, the provider reports

The type provider 'Rezoom.SQL.Provider.Provider' reported an error: The type initializer for '<StartupCode$Rezoom-SQL-Compiler>.$Rezoom.SQL.Compiler.Config' threw an exception

for the line type MyModel = SQLModel<".">

It seems to be a problem with the FParsec dependency, because pinning at 0.4.0 fixes it.

Given the query

type UserCount = SQL<"Select count(*) as cnt from Users">

UserCount.Command has signature Command<IReadOnlyList<Row>>

Is there a way to write the query such that the output is Command<Row> so .Scalar() can be called on it instead of .Plan() or .ExactlyOne()?

Currently using .ExactlyOne() though.

@rspeele would you mind giving a look at smoothdeveloper#1

I've implemented basic stuff to get create schema and drop schema parsing and translation.

I'm wondering if there is support in the library to explicitly avoid such feature with specific backends? SQLite won't support the generated statements.

Motivation

There are some constructs that are legal SQL but are almost certainly mistakes if found in a static, handwritten query.

The primary examples that come to mind are:

NULL comparison is always false.

select * from Foo where Bar = null
-- `expr = null` is always false, should probably be `Bar IS null`

UPDATE/DELETE without WHERE clause is usually a mistake

update User set Email = @newEmail
-- uhhh... you probably forgot to put `where Id = @userId`

Idea

RZSQL should have some "linters" that implement a visitor for SQL statements and expressions (like ASTMapping.fs but without any output). There should be a separate linter class for each type of bogus construct we can think of.

In rzsql.json there could be an option like so, to determine which linters will run on the SQL statements in the project.

    "constructs": {
         "allowed": ["MissingWhereClause"],
         "banned": ["NullComparison", "UnsafeInjectRawSQL"]
    }  

This would let you override the linters enabled/disabled. Having both a whitelist and blacklist would let us pick a reasonable set of default linters that wouldn't be overly strict or overly lenient.

Linters would be named after the construct they throw an error upon recognizing. This way the constructs configuration setting reads like a list of what kinds of SQL are allowed and banned in your project. The goal here is to avoid the double-negative confusion that would result from something like the below, where we are effectively saying "we DON'T want the linter that checks for NOT having a where clause, because we DO want those statements in our SQL code".

    "linters": {
         "blacklist": ["NoMissingWhereClause"],
         "whitelist": ["NullComparisonAlwaysFalse", "NoUnsafeInjectRawSQL"]
    }

Trying to get this working with an existing SQL Server DB

Code is as follows

namespace JPS.Core
module DataAccess =
    open FSharp.Data.Sql
    open Rezoom
    open Rezoom.SQL
    open Rezoom.SQL.Synchronous

    type Log = SQL<"""
        SELECT * from Logs
        """>

    let test =
        use connection = new ConnectionContext()        
        ()

Only thing is I'm getting the following error no matter what I try. I can connect to the DB with my connection string though SQL Server MMS and can query tables. Also tried using FSharp.Data.Sql which appears to work fine... Error is

C:**\DataAccess.fs(8,16): error FS3033: The type provider 'Rezoom.SQL.Provider.Provider' reported an error: SQ011: No such object: Logs.�Log(2,23):�� SELECT * from ⇨ [Logs] ⇦

Any Idea what I'm doing wrong or how I can get more information out to debug?

Hi,

I've been researching Rezoom because I want to access some SQL Server stored procedures but I'm not able to find anything on the documentation for it. Is it supported?

Thanks!
Erlis

Query part like:
where i.Id in @ìds
Compiles with empty ids list to:
WHERE ([i].[Id] IN ());
Which is syntax error:
Msg 102, Level 15, State 1, Line #
Incorrect syntax near ')'.

Thanks for this awesome library!

I'm having some trouble using it from FSI, or even in a project with recent tooling (vs2019, but I assume same issue happens in vs2017):

it either throws internal error in Config type initializer, or complains about fparsec-cs not being loaded (not sure if it was a binding redirect issue, but should work from fsi at least)

Interestingly, when I'm referencing the build from my PR rather than published nuget, it fixes the issue.

I've not figured if this is an issue in paket generated script, but I'd first like to check if using new sdk to target .net 45 binaries for the TP assemblies is what fixes the issue in my case.

Have you encountered the errors above?

edit: #43

This vendor-specific code

    vendor tsql {
        select *, checksum(*) as csum from person
    } imagine {
        select *, 0 as csum from person
    }

infers the type of the csum column as decimal type. Changing the column expression to cast(0 as int) changes the type to int.

From the documentation I expect digits with no decimal point to be interpreted as an int literal.

This is to track issues/tasks on the path to 1.0.1.

• Upgrade Rezoom to 1.0.1 (package not available)
• Upgrade FParsec-Pipes to 1.1.1 (gives an error)
• Test TSQL
• Test Sqlite
• Test Posgres
• Fix error when select has only 1 field

Hi

I am creating SQL query which insert potentially thousands of rows. Issue is that there is max length of parameters in query: yiisoft/yii2#10371

If actions doesn't return anything you can just combine bathing and non batching for loops:

plan {
    let chunks =
        data
        |> List.chunkBySize 100
    
    for chunk in chunks do
        for item in batch chunk do
            do! doSomething item
}

On the other hand you need to return data so concurrentList should(?) be used:

Plan.concurrentList
    [ for d in data ->
        doSomething d
    ]

But this batches all the work and may be fail for too big query. So if there would be Plan.nonConcurrentList I could do something like this:

let chunks =
    spots
    |> List.chunkBySize 100

let plans =
    chunks
    |> List.map (fun chunk -> 
        Plan.concurrentList
            [ for d in chunk ->
                doSomething d
            ]
        )

Plan.nonConcurrentList plans

Any hints?

A vendor script (tsql) with multiple statements fail to execute.

For this script

vendor tsql {
	CREATE NONCLUSTERED INDEX IX_Directories_Kind ON dbo.Directories
		(
		kind
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	GO
	CREATE NONCLUSTERED INDEX IX_Directories_localId ON dbo.Directories
		(
		localId
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	GO
	CREATE NONCLUSTERED INDEX IX_Directories_parentDir ON dbo.Directories
		(
		parentDir
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	GO
	CREATE NONCLUSTERED INDEX IX_Directories_localId_Kind ON dbo.Directories
		(
		localId,
		kind
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
};

alter table Closure add unique (parent, depth, child);

alter table Closure add unique (child, parent, depth);

The error is

System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.'

Work around

What works in this case was to wrap each statement in a vendor statement as below

vendor tsql {
	CREATE NONCLUSTERED INDEX IX_Directories_Kind ON dbo.Directories
		(
		kind
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
};

vendor tsql {
	CREATE NONCLUSTERED INDEX IX_Directories_localId ON dbo.Directories
		(
		localId
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
};

vendor tsql {
	CREATE NONCLUSTERED INDEX IX_Directories_parentDir ON dbo.Directories
		(
		parentDir
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
};

vendor tsql {
	CREATE NONCLUSTERED INDEX IX_Directories_localId_Kind ON dbo.Directories
		(
		localId,
		kind
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
};

alter table Closure add unique (parent, depth, child);

alter table Closure add unique (child, parent, depth);

Following the tutorial in a .net 4.7.2 console app with FSharp.Core 4.5, the generated code produces an exception

System.TypeInitializationException
  HResult=0x80131534
  Message=The type initializer for 'Rezoom.SQL.Mapping.CodeGeneration.ReaderTemplate`1' threw an exception.
  Source=Rezoom.SQL.Mapping
  StackTrace:
   at Rezoom.SQL.Mapping.CodeGeneration.ReaderTemplate`1.Template()
   at Rezoom.SQL.Compiler.BackendUtilities.DefaultMigrationBackend.GetMigrationsRun()
   at Rezoom.SQL.Migrations.MigrationUtilities.runMigrations(MigrationConfig config, IMigrationBackend backend, IEnumerable`1 migrationTrees)
   at Rezoom.SQL.Migrations.MigrationExtensions.Run(MigrationTree`1[] migrations, MigrationConfig config, FSharpFunc`2 backend)
   at Program.MyModel.Migrate(MigrationConfig config)
   at Program.migrate()
   at Program.main(String[] argv)

Inner Exception 1:
MissingMethodException: Method not found: 'Microsoft.FSharp.Core.FSharpFunc`2<S`1<!!0>,Microsoft.FSharp.Core.FSharpFunc`2<LicenseToCIL.IL,S`1<S`1<!!1>>>> LicenseToCIL.Ops.newobj'x(System.Reflection.ConstructorInfo)'.

It seems to be a problem with the LicenseToCIL dependency, because pinning at 0.3.0 fixes it.

Hi

I converted yesterday db schema to rezoom, run migrations, did paket update and made commit. Today I noticed it's all gone and initial migraiont was replaced by hello world one. Content of database is only evidence that yesterday I had something. I was able to replicate loss and paket update seems to be the thing.

Edit: Nuget packages used:
Rezoom.SQL.Provider
Rezoom.SQL.Provider.TSQL

I'm getting the following error when following the tutorial, specifically at the query section, with this line: ListUsers.Command().execute(context)
Error:
The type referenced through 'Rezoom.CacheInfo' is defined in an assembly that is not referenced. You must add a reference to assembly 'Rezoom'

If applicable, here's my packet.dependencies and paket.references

paket.dependencies:
source https://api.nuget.org/v3/index.json

nuget FSharp.Core
nuget FParsec >= 1.0.2 restriction: >= net461
nuget FParsec-Pipes >= 0.4.0 restriction: >= net461
nuget LicenseToCIL >= 0.3.0 restriction: >= net461
nuget Rezoom >= 0.4.2 restriction: >= net461
nuget Rezoom.SQL.Provider >= 0.7.2.40976 restriction: >= net461
nuget Rezoom.SQL.Provider.SQLite >= 0.7.0 restriction: >= net461
nuget System.Data.SQLite.Core >= 1.0.108.0 restriction: >= net461
nuget System.ValueTuple >= 4.4.0 restriction: >= net461

paket.refernces:
FParsec
FParsec-Pipes
LicenseToCIL
Rezoom
Rezoom.SQL.Provider
Rezoom.SQL.Provider.SQLite content: once
System.Data.SQLite.Core
System.ValueTuple
FSharp.Core

When I open a project in Visual Studio running with the .NET Standard branch of Rezoom.SQL I get the following error:

The type provider 'Rezoom.SQL.Provider.Provider' reported an error: The type initializer for '<StartupCode$Rezoom-SQL-Compiler>.$Rezoom.SQL.Compiler.Config' threw an exception.

Seems like a VS specific problem: Neither dotnet CLI nor VS Code have a problem with the project.

PS: I'm aware of the fact that this project seems to be a bit abandoned (no offense to you @rkosafo) but I hope a running .NET Standard (#15) version of this library will bring a lot more interest in this project.

I get the error below when I execute a query with limit specified as a variable.

System.Data.SqlClient.SqlException: 'Incorrect syntax near '@RZSQL_0'.'

To reproduce

open Rezoom.SQL
open Rezoom.SQL.Migrations
open Rezoom.SQL.Synchronous


type MyModel = SQLModel<".">


let migrate () =
  let config =
    { MigrationConfig.Default with 
        LogMigrationRan = fun m -> printfn "Run migrations: %s" m.MigrationName }
  MyModel.Migrate config


type ReadUsers = SQL<"Select name, email from Users limit @top">
let error () =
  use context = new ConnectionContext ()
  let users = ReadUsers.Command(5L).Execute context
  printfn "Users: %A" users


[<EntryPoint>]
let main argv = 
  migrate ()
  error ()
  0 // return an integer exit code

Using
Rezoom.SQL.Provider.TSQL v0.7.0
Rezoom.SQL.Provider v0.7.0.36923
Rezoom v0.4.2

Microsoft.Data.SQLite currently supports many platforms while System.Data.SQLite is windows-only. By supporting M.D.S we will have sqlite support on macos etc. Microsoft.Data.SQLite is also netstandard2 compatible (as opposed to S.D.S) for when #15 is getting addressed

@rspeele First of all, welcome to fsprojects!
I've restored your admin access to the repo.

Can you please:

• Add Maintainers section in the README.md like this one.
• Make the fsprojects nuget account a co-owner on any nuget packages associated with the repo.

Hi @rspeele,

Thanks a lot for the awesome work.

Today, When I tried to use Rezoom type provider in my Mac using Mono (both 5.0.1 & 5.2.0) I am getting an "Object reference not set to an instance" error

To reproduce it, open the TypeProviderUsers.sln file in the Rezoom.SQL in either visual studio code or VS for Mac

/~/temp/Rezoom.SQL/src/TypeProviderUsers/TypeProviderUser.SQLite/Shared.fs(18,18): Error FS3033: The type provider 'Rezoom.SQL.Provider.Provider' reported an error: Object reference not set to an instance of an object (FS3033) (TypeProviderUser.SQLite)

SQLLite Type Provider

Postgres Type Provider

I think the vast majority of queries in most applications should be fully static, but occasionally there is a real need to build SQL at runtime.

In a perfect world we would have a LINQ-style query builder able to benefit from the compile-time model, but that will take time to create and needs a lot more thought put into its design.

So, to avoid blocking people from getting things done, I'd like to allow including pieces of dynamic SQL in a mostly-static query. Currently I think this could work like so:

type MyQuery = SQL<"""
    select * from MyTable where inject_raw_sql(@x)
""">

open Rezoom.SQL.Raw // sql and arg functions

let exampleCommand() =
    let rawSql =
        [   sql "SomeColumn = "
            arg DateTime.UtcNow
            sql " and SomeOtherColumn <> "
            arg 3
        ]
    MyQuery.Command(x = rawSql)

The raw SQL would have to be written for the backend dialect. It would not go through RZSQL parsing/checking/translation at any time.

In this design, raw SQL would just be a special type, much like there is currently a special "list" type for expr IN @param. inject_raw_sql would be an erased function, whose only job is to inform the typechecker that the parameter's type is raw SQL.

Perhaps there could also be an option in rzsql.json to ban use of this function, for people who want to make sure their codebase definitely has no SQL injection.

A weird thing about this design is that because it uses a plain old bind parameter, it would only be possible to add raw SQL in places that you could use an expression. But since the raw SQL is totally unchecked, it could actually include extra clauses -- in the above example, there would be nothing stopping inject_raw_sql(@x) from adding an order by clause onto the query. This is a bit ugly.

Thoughts on this idea?

This seems cool. Many times users start with SQLProvider because it's fast to start with, and works fine with any size of database. But then at some point users may have a need to write a manually for what-ever reason. Linq is not perfect. For now, the solution has been to write a stored procedure, which is unoptimal as other program logic is in F#-code. So I can see these two could work well side-by-side on a project.

The main drawback of FSharp.Data.SqlClient has been lack of other databases.
But they have a good comparison page, this could be added there, just send a PR and try to be objective: http://fsprojects.github.io/FSharp.Data.SqlClient/comparison.html

Are you planning to support Mysql and Oracle?

This may not be what you're talking about in the docs, but I would like to be able to use type converters, especially for managing things like IDs, which I typically wrap in a single-case union.

I have a thin SQL wrapper I wrote that lets me add converter functions to automatically serialize/deserialize when I run a query. This is especially useful with SQLite's much lighter type system. If I could do something like that in Rezoom.SQL, I would immediately jump ship and never look back.

The following fails at runtime with System.Data.SqlClient.SqlException: 'Invalid column name 'code'.'

alter table Users add column code int default 0;
update Users set code = id + 1000;

This is a consistent behaviour with SSMS. This is solved using by separating the statement with GO. I do recall your explanation on how GO is used to virtually separate scripts so they are run individually. There is however no such equivalent statement. The current workaround is having the statements in different files.

Is it possible to have a statement that is equivalent to GO? Also, I'm not really sure if it is possible but if such codes can give an error at compile time, it will be very helpful since it will help with the statement that if it compiles, it works :)

Being able to support spatial queries and types would be AWESOME!

Using a migration file is nice with code-first. But, is it possible to automatically map the data structure by targeting a database?

I mean, all you need is a connection string and it will detect all your db objects (tables, views, stored procedures, etc...). What do you think?

I'm raising this issue, because I don't know where else to ask a question.

I think it would be wonderful to be able to define a Data Layer/Service abstraction using Rezoom.SQL with SQLite for Xamarin.Android.

However, I don't know if that is possible or if anyone has tried that approach before.

Have a nice day, and thank you for your awesome work!

I am doing data insert but hit a wall since so far I know there is no possibility to return inserted id with TSQL. My data is quite relation heavy and single unit of data may not be unique by itself but its about relationships. Also so far I know you cannot insert partial graphs at once.

So plan is:

1. Insert Car (which doesn't have any unique data per se)
2. Insert Wheels by CarID... But since we don't know id and we cannot identify our car... we are in trouble.

Only workaround I can imagine is simply opt-out from auto increment identifiers and use client side generated id (guid maybe) instead. Honestly I cannot even imagine almost any practical use case where id return wouldn't be necessary. How you people can live without this or is it just my workflow? 😄

There appears to be an increase in the resources (memory and processor) used when editing and saving sql files. The behavior has been observed with with VS2017 and VSCode. In the case of VS, there is often the need to restart it when the memory increases and causes the machine to slow down.

Is this a known issue? Are there any workarounds?

Right now there is no way to dynamically order queries.

This makes the library unsuitable for applications that let the user sort a paged table of records.
That's a lot of applications!

This should be solvable without giving up type safety. Here's the design I have in mind:

Any command that consists of a single SELECT statement which is not known to be a single-row SELECT should be dynamically orderable.

e.g.

type MyQuery = SQL<"select Column1, 1+1 as Column2 from SomeTable">

This means that it gets the following extra stuff in its generated type:

• A nested type MyQuery.By with a private constructor and an overrided ToString().
• Static getters MyQuery.By.Column1 and MyQuery.By.Column2, which return instances of MyQuery.By
• A static method MyQuery.By.OfString("Column1") which checks that the passed string is one of the statically known output column names. MyQuery.By.OfString(MyQuery.By.Column2.ToString()) should work.
• A static OrderBy method taking a MyQuery.By and a DU Ascending|Descending and returning a MyQuery.OrderedQuery
• MyQuery.OrderedQuery has an instance method ThenBy taking another by+direction, Page taking limit+offset
• MyQuery.OrderedQuery has an instance Command method which is like the regular MyQuery.Command(param1 = ...) method, but includes the orderings applied so far

Hypothetical usage:

type MyQuery = SQL<"select Column1, 1+1 as Column2 from SomeTable where Name like @name">

let example (conn : ConnectionContext) =
    MyQuery
        .OrderBy(MyQuery.By.Column1, Ascending)
        .ThenBy(MyQuery.By.Column2, Descending)
        .Page(25, 50)
        .Command(name = "%a%")
        .Execute(conn)

I think it would be OK to limit to one ThenBy clause. Orderings more complicated than that don't make much sense to generate dynamically.

Thoughts?

Given the query

Select *
From Users u
Inner Join Comments c on u.id = c.authorId

The id field is duplicated. In the final object, Comments.Id is used while User.id is ignored. Is it possible to have it fail as it will be easy to get the wrong data?

This however gives the right exception

Select id, *
From Users u
Inner Join Comments c on u.id = c.authorId

It must be noted that the current behaviour is consistent though with SSMS.

When an Insert statement has a column duplicating, it results in a runtime error instead of a design time one.

insert into supportkinds (created, created)
  values (getdate(), getdate())

Error

Msg 264, Level 16, State 1, Line 1
The column name 'created' is specified more than once in the SET clause or column list of an INSERT. 
A column cannot be assigned more than one value in the same clause. 
Modify the clause to make sure that a column is updated only once. 
If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

Hi,
How does one delete a column with a constraint - particularly foreign key?

memberId int64 null references Members(id)

Alter table ... drop column ... gives an error that the column cannot be dropped because it is referenced by constraints ... .

I'm not sure how to drop the constraint.

Work around

I used the vendor statement to delete the constraint and the column. The issue is what to put in imagine so that the column is removed. Using alter table ... drop column ... in imagine gives the same error as before.

For now, imagine is empty. The output from select * still has the deleted column.

What is the correct way to delete a column in this case?

Per #1 it would be nice to be able to target MySQL as a backend.

A PR for this would be welcome. #1 outlines how it would be done.
Alternatively if you want MySQL support but don't want to add it yourself just thumbs-up this issue to indicate your interest.

Hi,
Is there a way to increase the command timeout? I have some 2 stored procedures that take a little over 2 mins each and it appears calling them using the vendor statement fails. What is the right way to increase the command timeout?

When an inner join is written without on, it does not fail at compile time but rather give a runtime error like Incorrect syntax near ';'..

Using the default model and tsql,

module Tests =
  let print = printf "Output: %A"

  type InnerJoinWithoutOn = SQL<"""
    Select *
    From Users u
    Inner Join Comments c --ON is missing """>
  let innerJoinWithoutOn () = 
    use ctx = new ConnectionContext ()
    InnerJoinWithoutOn.Command().Execute (ctx) |> print

It will be nice if this can fail at compile time instead of at runtime.

Using the examples on the dynamic sql page

This works

type ExampleQueryResult =
    {   Id : int
        Name : string
        Email: string
    }

let exampleCommand (id : int) (nameSearch : string) =
    dynamicCommand<ExampleQueryResult>
        [|  sql "SELECT Id, Name, Email FROM USERS"
            sql " WHERE Id = "
            arg id
            sql " OR Name LIKE "
            arg ("%" + nameSearch + "%")
        |]

This fails

type MyMostlyStaticQuery = SQL<"""
    SELECT Id, Name, Email FROM USERS
    WHERE unsafe_inject_raw(@dynSql)
""">

let exampleCommand2 (id : int) (nameSearch : string) =
    let exampleSql =
        [|  sql "Id = "
            arg id
            sql " OR Name LIKE "
            arg ("%" + nameSearch + "%")
        |]
    MyMostlyStaticQuery.Command(dynSql = exampleSql)

Error is Incorrect syntax near '<'.

Calling the above 2 examples with args 1 and "Mojo", it appears the fragments for exampleCommand2 is invalid as below

Example 1 Fragments

[|CommandText "SELECT Id, Name, Email FROM USERS"; CommandText " WHERE Id = ";
  InlineParameter (Int32,1); CommandText " OR Name LIKE ";
  InlineParameter (String,"%Mojo%")|]

Example 2 Fragments

 [|CommandText "SELECT"; LineBreak; Indent; CommandText "[USERS].[Id]"; LineBreak; 
   CommandText ", [USERS].[Name]"; LineBreak; CommandText ", [USERS].[Email]"; 
   Outdent; LineBreak; CommandText "FROM "; Indent; CommandText "[USERS]"; 
   Outdent; LineBreak; CommandText "WHERE "; Indent; CommandText "(("; 
   Parameter 0; CommandText ")<>0)"; Outdent; CommandText ";"; LineBreak|]

Is there a way to log or capture the final sql statement that is executed?
Is this error from the usage or a bug?

Hi,

@JordanMarr has created dacpac-support to SQLProvider.

The parsing code has been separated to different files so that you can reference them directly, if you'd like to also build a support for dacpac:

https://github.com/fsprojects/SQLProvider/blob/master/src/SQLProvider/Ssdt.Polyfills.fs
https://github.com/fsprojects/SQLProvider/blob/master/src/SQLProvider/Ssdt.DacpacParser.fs

With paket, you could easily include them into your project and still get our updates when you want (just like Nuget packages), just use them as file-references in the paket.dependencies:

group SourceFiles
    source https://nuget.org/api/v2

    github fsprojects/SQLProvider src/SQLProvider/Ssdt.Polyfills.fs
    github fsprojects/SQLProvider src/SQLProvider/Ssdt.DacpacParser.fs

I don't know if Nuget is supporting direct file references.

Here's a simple scenario: you have a very basic table and you want to perform basic operations on it (SELECT, INSERT, UPDATE, DELETE), e.g.:

type Adapter = TableAdapter<"SomeTable">

That means we get this code generated, roughly:

type Adapter = {
     Select: SQL<"select * from SomeTable">
     Insert: SQL<"insert into SomeTable(...) values (...)">
     Update: SQL<"update SomeTable set (...) where ...">
     Delete: SQL<"delete SomeTable where ...">
   }

Should be enough for a dead-simple CRUD requirement.

This could get complicated with default values etc., so it should be sufficient to simply mandate usage of all columns. For a more complicated scenario, Date&Darwen's Tutorial D view update system could be considered (but I think it's not that useful in practice).

What do you think?

PostgreSQL supports arrays as a datatype:
https://www.postgresql.org/docs/9.1/arrays.html

For instance, a table blogposts could contain a column named tags which would be of type text[].

I want to write a migration where a table is created with such field. I couldn't find anything about arrays in Rezoom, is there something I missed or any workaround for it?

EDIT: OK, I just found https://rspeele.gitbooks.io/rezoom-sql/doc/Language/Functions/PostgresFunctions.html noting that arrays are not supported nor planned. Still, I would be glad to see a workaround (by using Postgre's arrays feature).

Given

    ( Id int primary key autoincrement
    , Email string(254) unique
    , Name string(64) null
    );

create table Comments
    ( Id int primary key autoincrement
    , AuthorId int references Users(Id)
    , Comment string(512)
    );

create index IX_Comments_AuthorId on Comments
    (AuthorId);

-- initial data
Insert into Users (email, name)
Values ('[email protected]', 'Mr. A'), ('[email protected]', 'Mr. B');

Insert into Comments (authorId, comment)
Values (1, 'This is cool'), (1, 'This is not cool');

The code below fails with Data is Null. This method or property cannot be called on Null values.

module Tests =
  let print x = printf "Output: %A" x

  type LeftJoinWithManyFails = SQL<"""
    Select u.id, many comments (c.id, c.comment)
    From Users u
    left Join Comments c on u.id = c.authorId""">
  let leftJoinWithManyFails () =
    LeftJoinWithManyFails.Command().Execute (new ConnectionContext()) |> print

The expectation was to have comments as an empty list. Is this possible?

Hi there,

Any plan to support .net core 2

Thanks

Batching large queries with TSQL will fail to SqlException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

Bug can be replicated by using hello world model provided by Rezoom.SQL.Provider & Rezoom.SQL.Provider.TSQL. Issue has been replicated to large number of different queries. Sample:

open Rezoom
open Rezoom.SQL
open Rezoom.SQL.Plans
open Rezoom.SQL.Migrations
open Rezoom.Execution
open Rezoom.PlanBuilder

type MyModel = SQLModel<".">

type InsertUser = SQL<"""
    insert into Users(Name, Email) values (@name, @email);
""">

[<EntryPoint>]
let main _ =
    MyModel.Migrate MigrationConfig.Default
    let data = List.init 2000 (fun i -> i.ToString() + "@test.com", None)
    let task =
        plan {
            for (email, name) in batch data do
                do! InsertUser.Command(email, name).Plan()
        }
        |> execute ExecutionConfig.Default
    task.Wait()
    0

This bug was noticed in issue #26 but since it was orginally asked as how-to question I will closed that and opened more clear bug report. As workaround batching and non-batching operations can be manually be used.

Btw library feels pretty stable now in general and we are currently using that for couple of real projects. How about nuget bump after this?

I'd like to use Rezoom.SQL as command-line tool: you give it your migrations, and it generates some source code for you (or indeed a minimal assembly).

Pros:

• usage on constrainted platforms (I'm looking at .NET CF primarily, but also Mono, Xamarin, etc.)
• usage in the wider .NET ecosystem (painless interop with C#, VB -- and it also makes it easier to introduce F# in a project)

Cons:

• needs a separate build step
• more difficult to iterate compared to F# type provider (still much better, I think, than the hand-written DAL where you need tests to ensure that everything works as expected)

let loadUserModel () =
    let assemblyFolder = Path.GetDirectoryName(Uri(Assembly.GetExecutingAssembly().CodeBase).LocalPath)
    let resolutionFolder = Path.Combine(assemblyFolder, "../../../TempDBImpl")
    UserModel.Load(resolutionFolder, ".")

let migrate connectionString =
    let backend : IBackend = unbox (Rezoom.SQL.Compiler.Postgres.PostgresBackend())
    let connection = Configuration.ConnectionStringSettings("Test", connectionString)
    connection.ProviderName <- "Npgsql"
    let result = <@ (%backend.MigrationBackend) connection @>
    let migrationBackend = result.Evaluate()
    let model = loadUserModel()
    MigrationExtensions.Run ((model.Migrations.ToArray()), MigrationConfig.Default, fun () -> migrationBackend)

Note that this requires pulling in a library just to execute the expressions ( result.Evaluate() ).

It would be great if SQLModel<".">.Migrate() had an overload to take a connection string instead of just the name of a connectionName from a config.

Is there a way to indicate or determine the sql file responsible for an error during migration?

When there are migration errors (from using the vendor statement a lot), the exception shown is the correct sql error but when multiple sql files were added running the application, it can be a bit difficult to identify which file generated the error.

Example error

Is there way / plans to create types reusable?

If types have somewhat big complexity and there is lot of queries which have different filter / order logic but result data is identical you still have to duplicate mapping.

Minimal example:

type private getItemsQuery = SQL<"""
    select *
    from Item i
""">

type private getActiveItemsQuery = SQL<"""
    select *
    from Item i
    where i.IsActive = true
""">

//How to make this available for both queries?
let mapItem (dbRow : ???.Row) =
    (dbRow.ItemId, dbRow.DataField)

let getItems () =
    use context = new ConnectionContext()
    getItemsQuery.Command().Execute(context)
    result |> Seq.map mapItem

let getActiveItems () =
    use context = new ConnectionContext()
    getActiveItemsQuery.Command().Execute(context)
    result |> Seq.map mapItem

Per #1 it would be nice to be able to target Oracle as a backend.

A PR for this would be welcome. #1 outlines how it would be done.
Alternatively if you want Oracle support but don't want to add it yourself just thumbs-up this issue to indicate your interest.