Vp.FSharp.Sql

Vp.FSharp.Sql

F# 및 모든 ADO 제공 업체와 함께 일할 수있는 핵심 라이브러리.

대부분의 경우이 라이브러리는 관련 Ado 제공 업체를 활용하는 다른 F# 라이브러리를 작성하는 데만 사용됩니다.

SQL 명령 A-LA-F#을 실행하려면이 섹션을 살펴볼 수 있습니다.

슬래 깅 과대 광고

우리는 "매우 논란의 여지가있는 관행"을 따라 우리의 능력을 최대한 활용합니다!

상태	패키지
좋아요
OK (sorta)
TBD
TBD

Nuget 패키지

이름	버전	명령
Vp.FSharp.Sql		Install-Package Vp.FSharp.Sql

이 라이브러리를 사용하는 방법?

이 라이브러리는 주로 관련 ADO.NET 제공 업체와 함께 다른 라이브러리를 구축하기위한 기초가되어 강력한 경험을 제공하는 것을 목표로합니다.

아래 라이브러리를 확인할 수 있으며 각 라이브러리를 확인할 수 있습니다. 각 라이브러리는 Vp.FSharp.Sql 활용하고 관련 ADO.NET 제공 업체를 확인할 수 있습니다.

이름	ado.net 제공자	버전	명령
Vp.FSharp.Sql.Sqlite	System.Data.SQLite.Core		Install-Package Vp.FSharp.Sql.Sqlite
Vp.FSharp.Sql.SqlServer	Microsoft.Data.SqlClient		Install-Package Vp.FSharp.Sql.SqlServer
Vp.FSharp.Sql.PostgreSql	Npgsql		Install-Package Vp.FSharp.Sql.PostgreSql

간단히 말해서, 당신은 자신만의 완전한 공급자를 만들 수 있지만, 필요한 것만으로 만 갈 수 있습니다.

Vp.FSharp.Sql.Sqlite 공급자 구현을 연습합시다.

데이터베이스 값 유형

먼저 가장 중요한 유형의 데이터베이스 값 유형이 필요합니다.

sqlite의 경우, SqliteDbValue 간단한 차별 연합 (DU)으로 모델링 할 수 있습니다.

 /// Native SQLite DB types.
/// See https://www.sqlite.org/datatype3.html
type SqliteDbValue =
    | Null
    | Integer of int64
    | Real of double
    | Text of string
    | Blob of byte array

^{스 니펫 소스 | 닻}

이러한 사례는 공식 SQLITE 문서 후에 작성됩니다.

DB 매개 변수 변환에 대한 DB 값

이곳에서 공개 API에 노출 된 DU를 핵심 라이브러리 기능에서 소비 할 수있는 실제 DbParameter 컴퓨터 클래스로 변환합니다.

대부분의 시나리오에서 구현은 다양한 데이터베이스 값 유형 케이스에 패턴 일치를 작성하고 ADO.NET 제공 업체에서 사용 가능한 관련 DbParameter 특정 유형을 작성하는 것으로 구성됩니다.

 let dbValueToParameter name value =
    let parameter = SQLiteParameter ()
    parameter.ParameterName <- name
    match value with
    | Null ->
        parameter.TypeName <- ( nameof Null ) .ToUpperInvariant ()
    | Integer value ->
        parameter.TypeName <- ( nameof Integer ) .ToUpperInvariant ()
        parameter.Value <- value
    | Real value ->
        parameter.TypeName <- ( nameof Real ) .ToUpperInvariant ()
        parameter.Value <- value
    | Text value ->
        parameter.TypeName <- ( nameof Text ) .ToUpperInvariant ()
        parameter.Value <- value
    | Blob value ->
        parameter.TypeName <- ( nameof Blob ) .ToUpperInvariant ()
        parameter.Value <- value
    parameter

참고 :이 기능은 공개 일 필요는 없으며 DU 만 공개해야합니다.

바인딩 종속성 : 유형 및 기능

SqlDependencies 가장 중요한 기본 연합 작업을 모두 고수하는 접착제처럼 작용합니다.

 /// SQLite Dependencies
type SqliteDependencies =
    SqlDependencies <
        SQLiteConnection ,
        SQLiteCommand ,
        SQLiteParameter ,
        SQLiteDataReader ,
        SQLiteTransaction ,
        SqliteDbValue >

이 유형의 인스턴스는 다음과 같이 구현할 수 있습니다.

 let beginTransactionAsync ( connection : SQLiteConnection ) ( isolationLevel : IsolationLevel ) _ =
    ValueTask.FromResult ( connection.BeginTransaction ( isolationLevel ))

let executeReaderAsync ( command : SQLiteCommand ) _ =
    Task.FromResult ( command.ExecuteReader ())

{ CreateCommand = fun connection -> connection.CreateCommand ()
  SetCommandTransaction = fun command transaction -> command.Transaction <- transaction
  BeginTransaction = fun connection -> connection.BeginTransaction
  BeginTransactionAsync = beginTransactionAsync
  ExecuteReader = fun command -> command.ExecuteReader ()
  ExecuteReaderAsync = executeReaderAsync
  DbValueToParameter = Constants.DbValueToParameter }

이 특별한 경우 System.Data.SQLite 는 가장 구체적인 유형은 비 동기성 API를 통해서만 사용할 수 있습니다.

예를 들어, 반환 유형이 가장 구체적이므로 command.ExecuteDbDataReader 대신 command.ExecuteReader 사용합니다.

• SQLiteCommand.ExecuteDbDataReader()
• SQLiteCommand.ExecuteReader()

또한, 비동기 API의 발생이 없다는 것을 알 수 있듯이, 비동기 "구현"(또는 그 부족)이 기본 클래스 구현에 의존한다는 것을 의미합니다.

• DbCommand.ExecuteReaderAsync()
• DbCommand.ExecuteDbDataReader(CommandBehavior behavior)

동기 버전 주변의 비동기 래퍼입니다.

마찬가지로 connection.BeginTransaction 대신 command.BeginTransactionAsync :

• SQLiteConnection.BeginTransaction()
• DbConnection.BeginTransactionAsync()

이 예제만으로도 가장 많이 사용 가능한 ADO.NET 제공 업체 구현에서 찾을 수있는 불일치를 보여줍니다.

⌨ 명령 정의

단순성을 위해 관련 Ado 제공자 유형을 사용하여 CommandDefinition 유형을 일종의 유형 바인더로 제한 할 수 있습니다.

 /// SQLite Command Definition
type SqliteCommandDefinition =
    CommandDefinition <
        SQLiteConnection ,
        SQLiteCommand ,
        SQLiteParameter ,
        SQLiteDataReader ,
        SQLiteTransaction ,
        SqliteDbValue >

이는 나중에 CommandDefinition 매개 변수 중 하나로 받아들이는 SqlCommand 함수와 함께 사용될 수 있습니다.

구성

일반적인 제약 측면에서 또 다른 전문화가 있습니다.

 /// SQLite Configuration
type SqliteConfiguration =
    SqlConfigurationCache <
        SQLiteConnection ,
        SQLiteCommand >

이 유형은 유형의 또 다른 바인더이며 캐시 역할을합니다. 명령을 실행할 때 명령 정의와 함께 전달됩니다.

명령 구성

이것은 매우 간단합니다. 당신이해야 할 일은 다음과 같습니다.

• 새 모듈을 만듭니다 (원하는 경우).
• 라이브러리와 관련된 시공 기능을 정의하고 명령 정의를 SqlCommand 코어 함수로 전달하십시오.

 [<RequireQualifiedAccess>]
module Vp.FSharp.Sql.Sqlite.SqliteCommand

open Vp. FSharp . Sql


/// Initialize a new command definition with the given text contained in the given string.
let text value : SqliteCommandDefinition =
    SqlCommand.text value

/// Initialize a new command definition with the given text spanning over several strings (ie. list).
let textFromList value : SqliteCommandDefinition =
    SqlCommand.textFromList value

/// Update the command definition so that when executing the command, it doesn't use any logger.
/// Be it the default one (Global, if any.) or a previously overriden one.
let noLogger commandDefinition = { commandDefinition with Logger = LoggerKind.Nothing }

/// Update the command definition so that when executing the command, it use the given overriding logger.
/// instead of the default one, aka the Global logger, if any.
let overrideLogger value commandDefinition = { commandDefinition with Logger = LoggerKind.Override value }

/// Update the command definition with the given parameters.
let parameters value ( commandDefinition : SqliteCommandDefinition ) : SqliteCommandDefinition =
    SqlCommand.parameters value commandDefinition

/// Update the command definition with the given cancellation token.
let cancellationToken value ( commandDefinition : SqliteCommandDefinition ) : SqliteCommandDefinition =
    SqlCommand.cancellationToken value commandDefinition

/// Update the command definition with the given timeout.
/// Note: kludged because SQLite doesn't support per-command timeout values.
let timeout value ( commandDefinition : SqliteCommandDefinition ) : SqliteCommandDefinition =
    SqlCommand.timeout value commandDefinition

/// Update the command definition and sets whether the command should be prepared or not.
let prepare value ( commandDefinition : SqliteCommandDefinition ) : SqliteCommandDefinition =
    SqlCommand.prepare value commandDefinition

/// Update the command definition and sets whether the command should be wrapped in the given transaction.
let transaction value ( commandDefinition : SqliteCommandDefinition ) : SqliteCommandDefinition =
    SqlCommand.transaction value commandDefinition

명령 실행

마찬가지로, 명령 실행은 동일한 원칙을 따릅니다. 즉, 관련 강한 유형 매개 변수 (현재 및 특정 ADO.NET 제공자에 해당)를 SQLCommand Core 기능에 전달합니다.

 /// Execute the command and return the sets of rows as an AsyncSeq accordingly to the command definition.
/// This function runs asynchronously.
let queryAsyncSeq connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.queryAsyncSeq
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the sets of rows as an AsyncSeq accordingly to the command definition.
/// This function runs synchronously.
let querySeqSync connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySeqSync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the sets of rows as a list accordingly to the command definition.
/// This function runs asynchronously.
let queryList connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.queryList
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the sets of rows as a list accordingly to the command definition.
/// This function runs synchronously.
let queryListSync connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.queryListSync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the first set of rows as a list accordingly to the command definition.
/// This function runs asynchronously.
let querySetList connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetList
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the first set of rows as a list accordingly to the command definition.
/// This function runs synchronously.
let querySetListSync connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetListSync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the 2 first sets of rows as a tuple of 2 lists accordingly to the command definition.
/// This function runs asynchronously.
let querySetList2 connection read1 read2 ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetList2
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read1 read2 commandDefinition

/// Execute the command and return the 2 first sets of rows as a tuple of 2 lists accordingly to the command definition.
/// This function runs synchronously.
let querySetList2Sync connection read1 read2 ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetList2Sync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read1 read2 commandDefinition

/// Execute the command and return the 3 first sets of rows as a tuple of 3 lists accordingly to the command definition.
/// This function runs asynchronously.
let querySetList3 connection read1 read2 read3 ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetList3
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read1 read2 read3 commandDefinition

/// Execute the command and return the 3 first sets of rows as a tuple of 3 lists accordingly to the command definition.
/// This function runs synchronously.
let querySetList3Sync  connection read1 read2 read3 ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetList3Sync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read1 read2 read3 commandDefinition

/// Execute the command accordingly to its definition and,
/// - return the first cell value, if it is available and of the given type.
/// - throw an exception, otherwise.
/// This function runs asynchronously.
let executeScalar < 'Scalar > connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeScalar < 'Scalar , _, _, _, _, _, _, _, _>
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

/// Execute the command accordingly to its definition and,
/// - return the first cell value, if it is available and of the given type.
/// - throw an exception, otherwise.
/// This function runs synchronously.
let executeScalarSync < 'Scalar > connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeScalarSync < 'Scalar , _, _, _, _, _, _, _, _>
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

/// Execute the command accordingly to its definition and,
/// - return Some, if the first cell is available and of the given type.
/// - return None, if first cell is DBNull.
/// - throw an exception, otherwise.
/// This function runs asynchronously.
let executeScalarOrNone < 'Scalar > connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeScalarOrNone < 'Scalar , _, _, _, _, _, _, _, _>
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

/// Execute the command accordingly to its definition and,
/// - return Some, if the first cell is available and of the given type.
/// - return None, if first cell is DBNull.
/// - throw an exception, otherwise.
/// This function runs synchronously.
let executeScalarOrNoneSync < 'Scalar > connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeScalarOrNoneSync < 'Scalar , _, _, _, _, _, _, _, _>
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

/// Execute the command accordingly to its definition and, return the number of rows affected.
/// This function runs asynchronously.
let executeNonQuery connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeNonQuery
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

/// Execute the command accordingly to its definition and, return the number of rows affected.
/// This function runs synchronously.
let executeNonQuerySync connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeNonQuerySync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

널 헬퍼

NULL 도우미를위한 다른 모듈을 만들 수 있으며 나머지는 관련 매개 변수를 기본 코어 함수로 전달하는 것입니다.

 [<RequireQualifiedAccess>]
module Vp.FSharp.Sql.Sqlite.SqliteNullDbValue

open Vp. FSharp . Sql


/// Return SQLite DB Null value if the given option is None, otherwise the underlying wrapped in Some.
let ifNone toDbValue = NullDbValue.ifNone toDbValue SqliteDbValue.Null

/// Return SQLite DB Null value if the option is Error, otherwise the underlying wrapped in Ok.
let ifError toDbValue = NullDbValue.ifError toDbValue ( fun _ -> SqliteDbValue.Null ) 

거래 도우미

여기서도 더 똑같습니다.

 [<RequireQualifiedAccess>]
module Vp.FSharp.Sql.Sqlite.SqliteTransaction

open Vp. FSharp . Sql
open Vp. FSharp . Sql . Sqlite


let private beginTransactionAsync = Constants.Deps.BeginTransactionAsync
let private beginTransaction = Constants.Deps.BeginTransaction

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// cancellation token and transaction body.
/// This function runs asynchronously.
let commit cancellationToken isolationLevel connection body =
    Transaction.commit cancellationToken isolationLevel connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// and transaction body.
/// This function runs synchronously.
let commitSync isolationLevel connection body =
    Transaction.commitSync isolationLevel connection beginTransaction body

/// Create and do not commit an automatically generated transaction with the given connection, isolation,
/// cancellation token and transaction body.
/// This function runs asynchronously.
let notCommit cancellationToken isolationLevel connection body =
    Transaction.notCommit cancellationToken isolationLevel connection beginTransactionAsync body

/// Create and do not commit an automatically generated transaction with the given connection, isolation,
/// and transaction body.
/// This function runs synchronously.
let notCommitSync isolationLevel connection body =
    Transaction.notCommitSync isolationLevel connection beginTransaction body

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// cancellation token and transaction body.
/// The commit phase only occurs if the transaction body returns Some.
/// This function runs asynchronously.
let commitOnSome cancellationToken isolationLevel connection body =
    Transaction.commitOnSome cancellationToken isolationLevel connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// and transaction body.
/// The commit phase only occurs if the transaction body returns Some.
/// This function runs synchronously.
let commitOnSomeSync isolationLevel connection body =
    Transaction.commitOnSomeSync isolationLevel connection beginTransaction body

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// cancellation token and transaction body.
/// The commit phase only occurs if the transaction body returns Ok.
/// This function runs asynchronously.
let commitOnOk cancellationToken isolationLevel connection body =
    Transaction.commitOnOk cancellationToken isolationLevel connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// and transaction body.
/// The commit phase only occurs if the transaction body returns Ok.
/// This function runs synchronously.
let commitOnOkSync isolationLevel connection body =
    Transaction.commitOnOkSync isolationLevel connection beginTransaction body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// This function runs asynchronously.
let defaultCommit connection body = Transaction.defaultCommit connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// This function runs synchronously.
let defaultCommitSync connection body = Transaction.defaultCommitSync connection beginTransaction body

/// Create and do not commit an automatically generated transaction with the given connection and transaction body.
/// This function runs asynchronously.
let defaultNotCommit connection body = Transaction.defaultNotCommit connection beginTransactionAsync body

/// Create and do not commit an automatically generated transaction with the given connection and transaction body.
/// This function runs synchronously.
let defaultNotCommitSync connection body = Transaction.defaultNotCommitSync connection beginTransaction body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// The commit phase only occurs if the transaction body returns Ok.
/// This function runs asynchronously.
let defaultCommitOnSome connection body = Transaction.defaultCommitOnSome connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// The commit phase only occurs if the transaction body returns Ok.
/// This function runs synchronously.
let defaultCommitOnSomeSync connection body = Transaction.defaultCommitOnSomeSync connection beginTransaction body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// The commit phase only occurs if the transaction body returns Some.
/// This function runs asynchronously.
let defaultCommitOnOk connection body = Transaction.defaultCommitOnOk connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// The commit phase only occurs if the transaction body returns Some.
/// This function runs synchronously.
let defaultCommitOnOkSync connection body = Transaction.defaultCommitOnOkSync connection beginTransaction body

그리고 Voila! 당신은 이제 모두 정착되어 좋아하는 데이터베이스에 대해 가장 거친 명령을 실행할 준비가되었습니다!

TransactionScope 스코프 도우미

이 도우미는 TransactionScope 지원하는 한 ADO.NET 제공 업체에 관계없이 작업합니다.

즉, 우리는 당신이 그 조력자를 사용하는 것을 강력히 낙담시킵니다 .

• .NET Core 시대 이후 분산 거래에 대한 지원은 아직 사용할 수 없습니다.
• TransactionScope 사용하여 (조력자가 있거나없는) 오류가 발생하기 쉬우므로 명확한 오류 메시지없이 예기치 않은 동작에 직면 할 수 있습니다.
• 이 지원과 관련하여 진화가 거의 없으므로 분산 트랜잭션에 대한 지원없이 TransactionScope 사용하는 응용 프로그램이 제한되어 있으므로 해당 도우미는 별도의 라이브러리 (즉, 저장소 + Nuget 패키지)로 이동할 수 있습니다.

2PC 또는 분산 거래에 대한 실행 가능한 해결 방법이 필요한 경우 SAGA 패턴과 같은 건축 패턴을 확인할 수 있습니다.

기여하는 방법

버그 보고서, 기능 요청 및 풀 요청을 매우 환영합니다!

시작하려면 기여 가이드 라인을 읽으십시오.

라이센스

이 프로젝트는 MIT에 따라 라이센스가 부여됩니다.

라이센스에 대한 자세한 내용은 라이센스 파일을 참조하십시오.