One of the things I haven't blogged about much is the great collection of F# projects up on CodePlex.

The one that caught my eye today is Soma - a "Sql Oriented Mapping Framework". (Note, Soma is documented in Japanese  - I have seen from Twitter and elsewhere the many F# tweets in Japanese, and its great to see a community developing there)

Soma implements an F#-oriented, code-first approach to O/R mapping. Queries are expressed in SQL strings, with some interesting use of F# quotations. Here's the description of the project from the home page, along with a sample that looks very compelling:

Soma is an O/R mapping framework develeped in F#.
Supported programming languages and RDBMS are followings:

SomaはF#で開発されたO/Rマッピングフレームワークです。
サポートされるプログラミング言語とRDBMSは次の通りです。

languages

  • F# 2.0
  • C# 4.0
  • Visual Basic 2010

RDBMS

  • Microsoft SQL Server 2008
  • MySQL 5.x.
  • Oracle Database 11g

Main Features

Main features are followings:

  • 2 way SQL - SQL is executable in programs and in sql tools out of the box
  • SQL log handling
  • automatic primary key generation
  • optimistic lock
  • pagination
  • support for F# immutable record type
  • support for muttable POCO
  • arbitrary SQL execution and result mapping
  • stored procedure call
  • no configuration file
  • stateless architecture


主要な機能は以下の通りです。

  • 2 way SQL - SQLはプログラムとツールで実行可能
  • SQLのログハンドリング
  • 主キーの自動生成
  • 楽観的ロック
  • ページング
  • F#のイミュータブルなレコード型のサポート
  • ミュータブルなPOCOのサポート
  • 任意のSQLの実行と結果のマッピング
  • ストアドプロシージャの実行
  • 設定ファイルを必要としない
  • ステートレスなアーキテクチャ

Sample Code in F#

Samples in other languages are included in the distribution zip file.

他の言語で作られたサンプルは配布zipファイルに含まれます。

open System
open System.Transactions
open Soma.Core

// define a module wraps Soma.Core.Db module
module MyDb = 
  let config = 
    { new MsSqlConfig() with
      member this.ConnectionString = "Data Source=.;Initial Catalog=Soma.Tutorial;Integrated Security=True" }
  let query<'T> = Db.query<'T> config
  let queryOnDemand<'T> = Db.queryOnDemand<'T> config
  let execute sql expr = Db.execute config sql expr
  let find<'T when 'T : not struct> = Db.find<'T> config
  let tryFind<'T when 'T : not struct> = Db.tryFind<'T> config
  let insert<'T when 'T : not struct> = Db.insert<'T> config
  let update<'T when 'T : not struct> = Db.update<'T> config
  let delete<'T when 'T : not struct> = Db.delete<'T> config
  let call<'T when 'T : not struct> = Db.call<'T> config

// define a record mapped to a table 
type Employee = 
  { [<Id(IdKind.Identity)>]
    EmployeeId : int 
    EmployeeName : string
    DepartmentId : int
    [<Version>]
    VersionNo : int }

// define a record mapped to a procedure
type ProcResultAndOut = 
  { EmployeeId : int
    [<ProcedureParam(Direction = Direction.Output)>]
    EmployeeCount : int
    [<ProcedureParam(Direction = Direction.Result)>]
    Employees : Employee list }

let main =
  // execute following code in a transaction, but don't commit
  use tx = new TransactionScope()
  
  // find by id
  let emp = MyDb.find<Employee> [1]
  printfn "FOUND RECORD : \n%A\n" emp

  // update
  let emp = MyDb.update { emp with EmployeeName = "Hoge" }
  printfn "UPDATED RECORD : \n%A\n" emp

  // delete
  MyDb.delete emp
  printfn "DELETED RECORD : \n%A\n" emp

  // insert
  let emp = MyDb.insert { EmployeeId = 0; EmployeeName = "Allen"; DepartmentId = 2; VersionNo = 0}
  printfn "INSERTED RECORD : \n%A\n" emp

  // query by SQL. parameters are bindable with "Code Quotations". 
  let empList = 
    MyDb.query<string * Employee> "
    select 
      d.DepartmentName, e.EmployeeId, e.EmployeeName, e.DepartmentId, e.VersionNo   
    from 
      Employee e 
    inner join 
      Department d 
    on 
      e.DepartmentId = d.DepartmentId 
    where 
      e.DepartmentId = /* emp.DepartmentId */0 
    " <@ let emp = emp in () @>
  printfn "QUERIED TUPLES : \n%A\n" empList

  // call procedure
  let result = MyDb.call<ProcResultAndOut> { EmployeeId = 1; EmployeeCount = 0; Employees = [] }
  printfn "PROCEDURE OUTPUT : \n%A\n" result

  // exequte arbitrary SQL
  let rows = 
    MyDb.execute "
    delete from Employee 
    " <@ () @>
  printfn "AFFECTED ROWS : \n%A\n" rows

  Console.ReadKey()