search
LoginSignup
1

More than 1 year has passed since last update.

Organization

ダッパーというのを触ってみた(Sqlite,F#)

Summary

F#でSQLを楽にかくのにはダッパーというのがいいらしい
ということで
ダッパーというのを触ってみました

ダッパーとは?

SQLiteとfsharpの橋渡し的な何か

コード

下記においてます

Dapperというのを触ってみた!

パッケージ

下記を dotnet add package する

dapper
system.data.sqlite

オープン

open System.Data.SQLite
open System.Collections.Generic
open System.Data
open System.Dynamic
open Dapper

便利関数

module Util =

  (*
    便利関数

    vbfox/Dapper.fs
    https://gist.github.com/vbfox/1e9f42f6dcdd9efd6660
    https://www.nekoni.net/Blog/Article/nekonidotnet-admin-development-part6-first-part

  *)

  let query<'Result> (sql:string) (connection:IDbConnection) : 'Result seq =
    connection.Query<'Result>(sql)

  let parametrizedQuery<'Result> (sql:string) (param:obj) (connection:IDbConnection) : 'Result seq =
    connection.Query<'Result>(sql, param)

  let parametrizedQuery2<'Result,'Result2,'R> (sql:string) (param:obj) (connection:IDbConnection)  =
    connection.Query<'R>(sql, param)

  let mapParametrizedQuery<'Result> (sql:string) (param : Map<string,_>) (connection:IDbConnection) : 'Result seq =
    let expando = ExpandoObject()
    let expandoDictionary = expando :> IDictionary<string,obj>
    for paramValue in param do
      expandoDictionary.Add(paramValue.Key, paramValue.Value :> obj)

    connection |> parametrizedQuery sql expando

  let execute (sql:string) (param:_) (connection:IDbConnection) =
    let response = connection.Execute(sql, param)
    printfn "response:%d" response
    response

  // 単純にレコードをseq<string>をに変換する
  // http://kiyokura.hateblo.jp/entry/2016/12/09/131602
  let simpleRecord (dapperSqlMapperDapperRows:seq<obj>) =
    dapperSqlMapperDapperRows
    |> Seq.map(
      fun (dapperSqlMapperDapperRow:obj)  ->
        dapperSqlMapperDapperRow :?> IDictionary<string, obj>
        |> Seq.map( fun (KeyValue(k,v)) -> v.ToString() )
    )


01.テーブルを作成する

image.png

image.png

  let private createTables () =

    let sql = @"

      DROP TABLE IF EXISTS [FruitsTable] ;
      CREATE TABLE [FruitsTable] (
          [商品CD] INTEGER
        , [商品名称]    TEXT
        , [色味]  TEXT
      )
      ;

      DROP TABLE IF EXISTS [PriceTable] ;
      CREATE TABLE [PriceTable] (
          [商品CD] INTEGER
        , [価格]  INTEGER
      )
      ;

    "
    new SQLiteConnection (@"URI=file:tmp.sqlite")
    |> Util.execute sql ""
    |> printfn "%A"

02. データを投入する

  let private insertDatas () =

    let sql = @"
      INSERT
        INTO [FruitsTable]
        VALUES( @id , @name , @color ) ;
    "
    let rcds = [ ["1";"りんご";"うすい赤"] ; ["2";"バナナ";"あざやかな黄色"] ; ["3";"チェリー";"黒みがかった赤"]  ]
    let queryParams = rcds |> Seq.map(fun rcd -> {|id = rcd.[0]; name = rcd.[1]; color = rcd.[2]|})
    let con = new SQLiteConnection (@"URI=file:tmp.sqlite")

    con
    |> Util.execute sql queryParams
    |> printfn "%A"

    let sql2 = @"
      INSERT
        INTO [PriceTable]
        VALUES( @id , @price ) ;
    "
    let rcds2= [ ["1";"250"] ; ["2";"120"] ; ["3";"450"] ]
    let queryParams2= rcds2 |> Seq.map(fun rcd -> {|id = rcd.[0]; price = rcd.[1]|})

    con
    |> Util.execute sql2 queryParams2
    |> printfn "%A"

03. テーブルからデータを取得する(テーブルスキーマを指定)

実行結果


seq [{ 商品CD = 1L
       商品名称 = "りんご"
       色味 = "うすい赤" }]

seq [{ 商品CD = 2L
       商品名称 = "バナナ"
       色味 = "あざやかな黄色" }]

seq [{ 商品CD = 3L
       商品名称 = "チェリー"
       色味 = "黒みがかった赤" }]

seq [{ 商品名称 = "チェリー"
       価格 = 450L }]

取得用のコード


  type Fruits =
    {
      ``商品CD`` : int64
      ``商品名称`` : string
      ``色味`` : string
    }

  type Price =
    {
      ``商品CD`` : int64
      ``価格`` : int64
    }

  type Foo =
    {
      ``商品名称`` : string
      ``価格`` : int64
    }

  // no query parameter
  let private select01 () =
    let sql = "SELECT * FROM [FruitsTable] WHERE [商品名称] = 'りんご' ;"
    use con = new SQLiteConnection (@"URI=file:tmp.sqlite")

    con
    |> Util.query<Fruits> sql
    |> printfn "%A"

  // query parameter is anonimous record type
  let private select02 () =
    let sql = "SELECT * FROM [FruitsTable] WHERE [商品名称] = @name ;"
    let parameter = "バナナ"
    let queryParams = {|name = parameter|}

    new SQLiteConnection (@"URI=file:tmp.sqlite")
    |> Util.parametrizedQuery<Fruits> sql queryParams
    |> printfn "%A"

  // query parameter is map
  let private select03 () =
    let sql = "SELECT * FROM [FruitsTable] WHERE [商品名称] = @name ;"
    let parameter = "チェリー"
    let queryParams = Map["name" , box parameter]

    new SQLiteConnection (@"URI=file:tmp.sqlite")
    |> Util.mapParametrizedQuery<Fruits> sql  queryParams
    |> printfn "%A"

  // Multi Mapping
  let private select04 () =
    let sql = @"
      SELECT
          ft.[商品名称]
        , pt.[価格]
      FROM [FruitsTable] AS ft
      INNER JOIN [PriceTable] AS pt ON
        ft.[商品CD] = pt.[商品CD]
      WHERE
        pt.[価格] > @price
      ;
    "
    let queryParams = {|price = 300|}

    new SQLiteConnection (@"URI=file:tmp.sqlite")
    |> Util.parametrizedQuery2<Fruits,Price,Foo> sql queryParams
    |> printfn "%A"

04. テーブルからデータを取得する(テーブルスキーマを使用しない)

実行結果

seq [seq ["1"; "りんご"; "うすい赤"]]

seq [seq ["2"; "バナナ"; "あざやかな黄色"]]

seq [seq ["3"; "チェリー"; "黒みがかった赤"]]

seq [seq ["チェリー"; "450"]]

取得用のコード


  // no query parameter
  let private selectA () =

    let sql = "SELECT * FROM FruitsTable WHERE 商品名称 = 'りんご' ;"

    use con = new SQLiteConnection (@"URI=file:tmp.sqlite")
    con.Query(sql)
    |> Util.simpleRecord
    |> printfn "%A"

  // query parameter is anonimous record type
  let private selectB () =

    let sql = "SELECT * FROM FruitsTable WHERE 商品名称 = @name ;"
    let parameter = "バナナ"
    let queryParams = {|name = parameter|}

    use con = new SQLiteConnection (@"URI=file:tmp.sqlite")
    con.Query(sql,queryParams)
    |> Util.simpleRecord
    |> printfn "%A"

  // query parameter is map
  let private selectC () =

    let sql = "SELECT * FROM FruitsTable WHERE 商品名称 = @name ;"
    let parameter = "チェリー"
    let queryParams = Map["name" , box parameter]

    use con = new SQLiteConnection (@"URI=file:tmp.sqlite")
    con.Query(sql,queryParams)
    |> Util.simpleRecord
    |> printfn "%A"

  // Multi Mapping
  let private selectD () =
    let sql = @"
      SELECT
          ft.[商品名称]
        , pt.[価格]
      FROM [FruitsTable] AS ft
      INNER JOIN [PriceTable] AS pt ON
        ft.[商品CD] = pt.[商品CD]
      WHERE
        pt.[価格] > @price
      ;
    "
    let queryParams = {|price = 300|}
    use con = new SQLiteConnection (@"URI=file:tmp.sqlite")
    con.Query(sql,queryParams)
    |> Util.simpleRecord
    |> printfn "%A"

参考

Dapper github
vbfox/Dapper.fs
猫に.NET - F#でDapperを使ったDBアクセス

感想

ほむほむ
お仕事コードがきれいになりそうな予感

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
1