Database.Esqueletoのサンプル

adv-cal2014.cabal

-- Initial adv-cal2014.cabal generated by cabal init.  For further 
-- documentation, see http://haskell.org/cabal/users-guide/

name:                adv-cal2014
version:             0.1.0.0
synopsis:            Esqueleto Example
-- description:         
license:             MIT
license-file:        LICENSE
author:              liquid_amber
maintainer:          liquid.amber.ja@gmail.com
-- copyright:           
-- category:            
build-type:          Simple
-- extra-source-files:  
cabal-version:       >=1.10

executable adv-cal2014
  main-is:             Main.hs
  -- other-modules:       
  other-extensions:    EmptyDataDecls, FlexibleContexts, GADTs, GeneralizedNewtypeDeriving, MultiParamTypeClasses, OverloadedStrings, QuasiQuotes, TemplateHaskell, TypeFamilies
  build-depends:       base >=4.6
                     , text >=1.2
                     , time >=1.4
                     , transformers >=0.3
                     , persistent >= 2.1
                     , persistent-template
                     , persistent-sqlite
                     , esqueleto
  -- hs-source-dirs:      
  default-language:    Haskell2010

LICENSE

Copyright (c) 2014 liquid_amber

Permission is hereby granted, free of charge, to any person obtaining
a copy of this software and associated documentation files (the
"Software"), to deal in the Software without restriction, including
without limitation the rights to use, copy, modify, merge, publish,
distribute, sublicense, and/or sell copies of the Software, and to
permit persons to whom the Software is furnished to do so, subject to
the following conditions:

The above copyright notice and this permission notice shall be included
in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Main.hs

{-# LANGUAGE EmptyDataDecls             #-}
{-# LANGUAGE FlexibleContexts           #-}
{-# LANGUAGE GADTs                      #-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}
{-# LANGUAGE MultiParamTypeClasses      #-}
{-# LANGUAGE OverloadedStrings          #-}
{-# LANGUAGE QuasiQuotes                #-}
{-# LANGUAGE TemplateHaskell            #-}
{-# LANGUAGE TypeFamilies               #-}

import           Data.Text (Text)
import           Data.Time.Clock (UTCTime(..))
import           Database.Persist.TH
import           Control.Monad.IO.Class  (liftIO)
import qualified Data.Text as T
import           Database.Persist
import           Database.Persist.Sqlite
import           Data.Time
import           Control.Monad (forM_)
import qualified Database.Esqueleto      as E
import           Database.Esqueleto      ((^.), (?.))

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Person
  mail Text
  UniqueMail mail
  deriving Show

Handle
  personId PersonId
  service Text
  name Text
  created UTCTime
  UniqueHandle personId service created
  deriving Show

Uri
  handleId HandleId
  uri Text
  label Text
  UniqueUri handleId label
  deriving Show
|]

main :: IO ()
main = do
  runSqlite ":memory:" $ do
    time <- liftIO getCurrentTime
    runMigration migrateAll

    johnId <- insert $ Person "john@example.com"
    janeId <- insert $ Person "jane@example.com"
    johnWId <- insert $ Person "john_watson@example.com"

    johnExampleId <- insert $ Handle johnId "example" "John Doe" time
    janeExampleId <- insert $ Handle janeId "example" "Jane Doe" time
    insert $ Handle johnId "irc" "john_doe" time
    johnAnotherId <- insert $ Handle johnWId "another" "John Watson" time
    insert $ Handle johnWId "irc" "xwhitex" time

    insert $ Uri johnExampleId "calendar" "http://example.com/calendar/john"
    insert $ Uri johnExampleId "address book" "http://example.com/address/john"
    insert $ Uri johnAnotherId "irc" "http://example.com/calendar/john_watson"

    liftIO $ putStrLn "use normal ==. operator"
    -- selectSource, selectKeysをつかってconduitで処理するほうが上品
    johnHandles <- selectList [HandleName ==. "John Doe"] [LimitTo 1]
    forM_ johnHandles $ \johnHandle -> do
      johnURIs <- selectList [UriHandleId ==. entityKey johnHandle] []
      liftIO $ print (johnURIs :: [Entity Uri])

    -- search john names
    liftIO $ putStrLn "use LIKE SQL statement"
    -- sqlite specificと思ったけどPostgresでもいいらしい, MySQLはだめ
    let s = "SELECT ??, ?? FROM handle LEFT OUTER JOIN uri ON handle.id = uri.handle_id WHERE handle.name LIKE '%' || ? || '%'"
    johnH2 <- rawSql s [toPersistValue ("john" `asTypeOf` T.empty )]
    liftIO $ forM_ (johnH2 :: [(Entity Handle, Maybe (Entity Uri))]) print

    -- use esqueleto
    liftIO $ putStrLn "use esqueleto"
    johnEs <-
      E.select
      $ E.from $ \(handle `E.LeftOuterJoin` muri) -> do
        E.on $ E.just (handle ^. HandleId) E.==. muri ?. UriHandleId
        E.where_ $ handle ^. HandleName `E.like` (E.%) E.++. E.val "john" E.++. (E.%)
        return (handle, muri)
    liftIO $ forM_ johnEs print

    -- サブクエリ使おう
    -- SELECT * FROM person INNER JOIN handle ON person.id == handle.person_id LEFT OUTER JOIN uri ON handle.id == uri.handle_id WHERE EXISTS (SELECT * FROM handle AS h2 WHERE person.id == h2.person_id AND (person.mail LIKE "%white%" OR h2.name LIKE "%white%"));
    liftIO $ putStrLn "get watson from white"
    watson <- E.select $ E.from $ \(person `E.InnerJoin` handle `E.LeftOuterJoin` muri) -> do
      E.on $ E.just (handle ^. HandleId) E.==. muri ?. UriHandleId
      E.on $ person ^. PersonId E.==. handle ^. HandlePersonId
      E.where_ $ E.exists $ E.from $ \handle -> do
        E.where_ $
          (person ^. PersonId E.==. handle ^. HandlePersonId)
          E.&&. ((person ^. PersonMail `E.like` (E.%) E.++. E.val "white" E.++. (E.%))
                 E.||. (handle ^. HandleName `E.like` (E.%) E.++. E.val "white" E.++. (E.%)))
      E.orderBy
        [ E.asc (person ^. PersonMail)
        , E.asc (handle ^. HandleService)
        , E.asc (handle ^. HandleName)
        , E.asc (muri ?. UriLabel)
        ]
      return (person, handle, muri)
    liftIO $ forM_ watson print

Setup.hs

import Distribution.Simple
main = defaultMain