[haskell][persistent][sqlite] Haskellでデータベースを扱うには（Query編）

前回のエントリでpersistentパッケージを用い、簡単なDBアクセスの手順（スキーマ定義、挿入、削除、単純なレコード取得）を紹介しました。今回は、Queryに関する情報をまとめておきます。

このエントリで紹介するQuery：

• IDを用いた単純な値（レコード）の取得：get
• UNIQUE制約のキーを用いた値（レコード）の取得：getBy
• 検索・ソート条件を自由に設定できる検索：selectX
• SQL直書きによる検索：rawQuery

準備：リストを用いてDBへレコードを一括挿入

• 様々なqueryの動作を確認するために、予め10件程度のデータをデータベースに格納しておきます。以下、コードになります。
• gender.hs

{-# LANGUAGE TemplateHaskell   #-}
module Gender where

import Database.Persist.TH

data Gender = Male | Female
    deriving (Show, Read, Eq)
derivePersistField "Gender"

• TemplateHaskellが生成したコードは、生成したのと同じmodule内で使用することはできないため、別ファイルで定義。
• person.hs

{-# LANGUAGE EmptyDataDecls    #-}
{-# LANGUAGE FlexibleContexts  #-}
{-# LANGUAGE GADTs             #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE QuasiQuotes       #-}
{-# LANGUAGE TemplateHaskell   #-}
{-# LANGUAGE TypeFamilies      #-}
import Control.Monad.IO.Class  (liftIO)
import Database.Persist
import Database.Persist.Sqlite
import Database.Persist.TH
import Data.Conduit
import qualified Data.Conduit.List as CL
import Gender

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Person
    name String
    age Int
    gender Gender -- Genderカラム（VARCHAR: Male or Female）
    Name name     -- nameカラムにUnique制約を付与
    deriving Show
|]

person_list = [(Person "Michael" 26 Male),
               (Person "Mark" 27 Male),
               (Person "Jhon" 55 Male),
               (Person "Cyndy" 25 Female),
               (Person "Amy" 30 Female),
               (Person "Linda" 19 Female),
               (Person "Steve" 42 Male),
               (Person "Dorothy" 37 Female),
               (Person "Robert" 40 Male),
               (Person "George" 15 Male)]

main :: IO ()
main = runSqlite "person.db" $ do
    runMigration migrateAll
    ids <- mapM insert person_list
    liftIO $ print ids
    return ()

• このプログラムを実行するとカレントディレクトリに10人分のデータが登録されたperson.dbというデータベースファイルが生成されます。PersonのリストをmapMで一括insertしています。

IDを用いた単純な値（レコード）の取得：get

• IDに対応する値がMaybeでラップされて返される。
• 値が存在しない場合はNothing。
• person.hsのmainに以下のコードを追加すれば、getの動作を確認できます。

  -- success to refer Michael
  michael <- get ((Key (PersistInt64 1)) :: Key (PersonGeneric SqlBackend))
  liftIO $ print michael
  -- fail to get with id. Nothing is returned
  noone <- get ((Key (PersistInt64 100)) :: Key (PersonGeneric SqlBackend))
  liftIO $ print noone

• printでMaybe Personの内容を表示しています。idに1を指定すると"Michael"のデータが返されます。idに100を指定した場合は該当するレコードが存在しないためNothingが返されます。

UNIQUE制約のキーを用いた値（レコード）の取得：getBy

• getとほぼ同じ。異なるのは以下の2点のみ。
1. IDの代わりにUNIQUE制約フィールドを表すUnique値を用いる
2. Valueの代わりにIDとValueが格納されたEntityが返される
• person.hsのmainを以下のように書き換えると、getByの動作を確認できます。

print_entity Nothing = do
  liftIO $ print "failed to obtain value..."
print_entity (Just (Entity key person)) = do
  liftIO $ print key
  liftIO $ print person

main :: IO ()
main = runSqlite "person.db" $ do
  runMigration migrateAll
  -- success to refer Cyndy
  cyndy <- getBy $ Name "Cyndy"
  liftIO $ print cyndy
  print_entity cyndy
  -- fail to get with Name. Nothing is returned
  noone <- getBy $ Name "NotFoundName"
  liftIO $ print noone
  print_entity noone

• print_entityでEntityの内容を表示しています。"Cyndy"という名前でgetByした場合には"Cyndyl"のデータが返されます。DBに存在しない"NotFoundName"で検索した場合には、Nothingが返され"failed to obtain value..."という文字列が出力されます。

検索・ソート条件を自由に設定できる検索：selectX

• select関連関数
• selectSource
• 引数：FilterのリストとSelectOptのリスト
• 返値：IDとValueが格納されたEntityリスト（conduitによるストリームデータ）
• selectList
• 引数：FilterのリストとSelectOptのリスト（selectSourceと同じ）
• 返値：IDとValueが格納されたEntityのリスト
• selectFirst
• 引数：FilterのリストとSelectOptのリスト（selectSourceと同じ）
• 返値：先頭のIDとValueを格納したEntity（空の場合はNothing）
• selectKeys
• 引数：Filterのリスト
• 返値：IDと指定カラム値（レコード全体は返されない）
• 引数の詳細
• Filter
• 結果を絞り込む条件をリストで表現
• equal（==.）
• not equal（!=.）
• more than or equal（>=.）
• more than（>.）
• less than or equal（<=.）
• less than（<.）
• is member（<-.）
• is not member（/<-.）
• AND/ORの表現
• AND：一つのリストの中にならべた条件はAND
• OR：リストとリストを（||.）で結ぶ
• SelectOpt
• ソート条件（Asc/Desc）
• 取得開始位置（OffsetBy）
• 取得結果数（LimitTo）
• selectListのサンプルです。

  -- 厄年（男性：25 or 52 or 62, 女性：19 or 33, 37）の人を検索し名前順で取得
  found <- selectList
           ( [PersonGender ==. Male, PersonAge <-. [25, 42, 61]]
             ||. [PersonGender ==. Female, PersonAge <-. [19, 33, 37]] )
           [ Asc PersonName ]
  -- 結果として得た Entity のリストを順に表示
  liftIO $ mapM print found

• 厄年に該当する人を名前順に並べて取得して表示するコードです。GenderにMail/Femail以外を指定したり、PersonAgeに数値以外の型を指定するとコンパイル時の型チェックでエラーを検出してくれます。

SQL直書きによる検索：rawQuery

• SQLを直に指定して検索することも可能です。
• raqQueryの型：
• rawQuery :: (MonadSqlPersist m, MonadResource m) => Text -> [PersistValue] -> Source m [PersistValue]
• mainの中に以下のコードを追加するとSQLを直に与えて、結果を得ることができます。

  -- 名前が"y"で終端するPersonを検索
  let sql = "SELECT name FROM Person WHERE name LIKE '%y'"
  rawQuery sql [] $$ CL.mapM_ (liftIO . print)

参考にしたサイト

• Hackage - Database.Persist.Query
• http://www.yesodweb.com/book/persistent
• Database.Persist の関数群を簡単に説明
• Insert list of values into database with persistent