[sqlite] SQLiteのロック・トランザクション関連仕様の整理

SQLiteは共有ロック・排他ロックの仕組みを備えており、プロセス内の複数スレッド、だけでなく、プロセスをまたぐ状態でSQLが同時に発行されても適切に処理されます。
トランザクションに複数のモードが存在し、指定するモードに依って取得されるロックが変わります。さらにSQLの内容に依存してロック状態が遷移するため、複数プロセスがアクセスしたときの挙動を正確に把握できるよう、仕様を整理してみました。
（文中の「プロセス」は、正確には「プロセス、もしくはスレッド」を意味します。）

DBのロック状態の種類：

• UNLOCKED
• ロックされていない状態。誰も読み書きしていない。DBの初期状態。
• SHARED
• read可、write不可な状態。複数プロセスが同時にSHAREDロックを取得可能。複数プロセスが同時にreadできることを意味する。DBがこの状態にあるとき、他のプロセスからのwriteはもちろん不可。
• RESERVED
• RESERVEDロックは、プロセスが将来writeを行う予定であるが、現時点ではreadだけを行っている状態である。SHAREDロックは複数プロセスが同時に取得することができるが、RESERVEDロックを取得できるのは1プロセスに限定される。
• PENDINGロックとの違いは、新たにSHAREDロックを取得できる点（RESERVEDロック中であれば、別プロセスが新たにSHAREDロックを取得できるが、PENDINGロック中は新たにSHAREDロックを取得することはできない）。
• SQL実行におけるwrite処理がキャッシュ上で完結する場合は、このRESERVEDロック状態で実行される。
• PENDING
• PENDINGロックは、既存のSHAREDロックの解放を待ち、EXCLUSIVEロックを取得してwriteを実行しようとしている状態である。PENDINGロック状態では新たなSHAREDロックの取得は許可されない。ただし、既存のSHAREDロックはそれが解放されるまでは有効。
• EXCLUSIVE
• EXCLUSIVEロックはDBファイルへのwrite実行時に必要なロックである。あるDBファイルの中では、ただ一つのEXCLUSIVEロックだけが有効になることができる（このとき、その他の全てのロックは解放された状態になる）。
• SQLiteは並列処理性能を最大化する目的で、EXCLUSIVEロックの取得時間を最小にするよう動作する。

参考： File Locking And Concurrency In SQLite Version 3

トランザクションの開始：

明示的にトランザクションを開始する、しないに関わらず、DBの更新ではかならずトランザクションが実行されます。

• BEGIN TRANSACTIONなしでDBを更新するSQLを発行した場合には、内部で自動的にトランザクションが開始され、SQL実行後にコミットされる
• BEGIN TRANSACTIONを明示的に実行した場合には、COMMITもしくはROLLBACKを実行するまでトランザクション状態が継続される

トランザクションのモード：

• DEFERRED
• デフォルトはこのモード。トランザクションを開始した後でも、実際にDBに対するアクセスが発生するまではどのロックも取得しない。最初のread処理でSHAREDロックを取得し、最初のwrite処理でRESERVEDロックを取得する。
• あるプロセスがトランザクションを開始した後に、別のプロセスが新たにトランザクションを開始できる（BEGIN TRANSACTION実行後に、別のプロセスからのBEGIN TRANSACTIONが成功してしまう点には注意が必要）。
• IMMEDIATE
• BEGIN TRANSACTION実行時にRESERVEDロックを取得する。あるdatabase connectionにおいてIMMEDIATEトランザクションを開始した後は、他のどのdatabase connectionも、DBへのwrite、IMMEDIATE/EXCLUSIVEトランザクションの開始はできなくなる。ただし、readは可能。
• EXCLUSIVE
• BEGIN TRANSACTION実行時にEXCLUSIVEロックを取得する。EXCLUSIVEトランザクション開始後は、”read uncomitted” connectionを除く全てのdatabase connectionでreadが禁止される。また、EXCLUSIVEトランザクション、例外なく全てのdatabase connectionにおいてwriteが禁止される。

参考： BEGIN TRANSACTION (SQL As Understood by SQLite)

ロック状態とトランザクションの関係

各トランザクション開始時にどのロックが取得されて、そのロック状態がどう遷移するのかがわかりにくかったため、絵にまとめました。

以上、SQLiteの動作理解の助けになれば幸いです。

[haskell][persistent][sqlite] Persistentパッケージ利用時にテーブルにインデックスを生成する方法

PersistentパッケージにはMigration機能が備わっており、自動的にテーブルを生成してくれます。スキーマ変更を行った際にも、変換が可能な限りテーブル内のレコードを保持したまま新しいスキーマに変換してくれます（Migration機能については過去のエントリでまとめています）。

自分が利用する上で、インデックスやトリガーを生成する手順が紹介されておらず困っていたのですが、rawExecuteという関数を用いることで自由にDDLを発行できることがわかりました。以下その手順とサンプルを紹介しておきます。

サンプルコード：

以下は、personテーブルのnameカラムにインデックスをs生成するサンプルです。runMigration実行直後に、runExecuteを実行することでインデックスを生成しています。このサンプルではインデックスを生成しているだけですが、同じ手順でトリガーの生成（CREATE TRIGGER）も可能です。

{-# LANGUAGE EmptyDataDecls             #-}
{-# LANGUAGE FlexibleContexts           #-}
{-# LANGUAGE GADTs                      #-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}
{-# LANGUAGE MultiParamTypeClasses      #-}
{-# LANGUAGE OverloadedStrings          #-}
{-# LANGUAGE QuasiQuotes                #-}
{-# LANGUAGE TemplateHaskell            #-}
{-# LANGUAGE TypeFamilies               #-}
import Control.Monad.IO.Class  (liftIO)
import Database.Persist         -- persistentパッケージ
import Database.Persist.Sqlite  -- persistent-sqliteパッケージ
import Database.Persist.TH      -- persistent-templateパッケージ

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Person
    name String
    age Int Maybe
    deriving Show
|]

main :: IO ()
main = runSqlite "test.db" $ do
    -- personテーブル生成
    runMigration migrateAll
    -- nameカラムにindexを生成する。placeholderがないため第2引数は[]でよい。
    -- 2回目以降、すでにインデックスが存在している場合のためにIF NOE EXISTSが必要。
    rawExecute "CREATE INDEX IF NOT EXISTS idx_name_on_person ON person(name);" [] -- here!

    michaelId <- insert $ Person "Michael" $ Just 26
    michael <- get michaelId
    liftIO $ print michael

runExecuteの第一引数には実行するSQLを記載します。既にインデックスが生成済みの場合には何も処理を行わないよう"IF NOT EXISTS"を指定しています。第二引数にはplaceholderの値を指定しますがこの例ではplaceholderは利用していないため"[]"を渡しています。
"IF NOT EXISTS"を指定しないと既に存在しているインデックスを再度生成しようとして、以下のようなエラーが報告されます。

*** Exception: SQLite3 returned ErrorError while attempting to perform prepare "CREATE INDEX idx_name_on_person ON person(name);": index idx_name_on_person already exists

補足：

Yesod BookのPersistentの解説には、rawQueryの利用手順が紹介されています。rawQueryとrawExecuteの型はそれぞれ以下のようになっています。

• rawQuery :: (MonadResource m, MonadReader env m, HasPersistBackend env SqlBackend) => Text -> [PersistValue] -> Source m [PersistValue]Source
• rawExecuteSource

  :: MonadIO m
  => Text	SQL statement, possibly with placeholders.
  -> [PersistValue]	Values to fill the placeholders.
  -> ReaderT SqlBackend m ()

• rawSqlSource

  :: (RawSql a, MonadIO m)
  => Text	SQL statement, possibly with placeholders.
  -> [PersistValue]	Values to fill the placeholders.
  -> ReaderT SqlBackend m [a]

rawQueryが実行結果を配列で返すのに対し、rawExecuteの結果はunit（空）となっています。このためDDL (Data Definition Language)実行時にはrawExecuteを利用するのがよいと思われます。
さらにrawSqlという関数もあるようですが、こちらの使い分けはよく分からず…。ご存じの方是非ご教授ください。m(_ _)m
（2015/12/16：追記）rawQueryはData.Conduit.Source型で結果を返します。これは結果が巨大であってもストリーム処理できることを意味しています。これに対しrawSqlは単純にリストで結果を返すという違いがあります。

参考：

• Persistent (Yesod book)
• Database.Persist.Sql (Hackage)
• [haskell][yesod][sqlite] Persistentパッケージのmigration機能のまとめ

[sqlite] WITHOUT ROWIDオプションを使った性能改善の解説

SQLite 3.8.2以降のバージョンでは、テーブル生成時のSQLに"WITHOUT ROWID"という指定ができるようになっているのをご存じでしょうか。この"WITHOUT ROWID"を利用することで、ストレージ使用量削減、処理高速化が可能です。
本家のページを参考に使用方法、どういう仕組みで何が最適化されるか、をまとめておきます。

使用方法：

テーブル生成時のSQLの末尾にWITHOUT ROWIDを追加するだけです（下線部）。
WITHOUT ROWIDを用いてテーブルを生成する際のSQL例：

CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
) WITHOUT ROWID;

ただし、WITHOUT ROWID指定時には以下の点に気をつけてください。

• PRIMARY KEYが必要
• INTEGER PRIMARY KEYの場合はWITHOUT ROWID指定なしでも同等の効果が得られる
• 以下の機能・APIが使えない
• AUTOINCREMENT
• sqlite3_last_insert_rowid()
• incremental blob I/O
• sqlite3_update_hook()

WITHOUT ROWID指定テーブルと通常テーブルの違い：

通常の（ROWID）テーブルを生成する際のSQL例：

CREATE TABLE IF NOT EXISTS old_wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
);
 

上記のような通常のSQLで生成したテーブルと、WITHOUT ROWID指定で生成したテーブルには以下の違いがあります。通常のROWIDテーブルとWITHOUT ROWIDテーブルとでは、ROWID値を保持するカラムをテーブル内部に生成するかどうか、という点が根本的に異なります。
通常（ROWID）テーブルは、PRIMARY KEYの指定の有無にかかわらず、必ず内部にROWIDカラムを生成し、ROWID用のB-Treeが構築されます（キー：ROWID、バリュー：word, cnt）。PRIMARY KEYが指定された場合には、ROWID用のB-Treeに加えて、PRIMARY KEY用のB-Treeがさらに構築されます（キー：word、バリュー：ROWID）。
これに対し、WITHOUT ROWIDテーブルではROWIDカラムが生成されなくなり、PRIMARY KEYで指定したカラムのB-Treeだけが構築されます（キー：word、バリュー：cnt）。
この違いにより例に挙げたテーブルでは（概算ですが）、必要ストレージが1/2になり、かつ、PRIMARY KEY指定でcntを参照する際の検索速度が2倍になる、メリットが得られます。なぜこのメリットが得られるのかを事項で説明します。

WITHOUT ROWIDテーブルで性能が改善される理由：

以下のSQLを実行を例にとって両テーブルの処理の違いを説明します。

SELECT cnt FROM wordcount WHERE word='xyzzy';

上記のSQL実行時、通常（ROWID）テーブルでは2つのB-Treeに対する捜査が必要になります。具体的には、最初にwordをキーに持つB-Treeを検索し、該当するROWIDを取り出します。その後、ROWIDをキーに持つB-Treeを検索することでcntを取り出します。
これに対しWITHOUT ROWIDテーブルではwordをキーに持つ、一つのB-Treeに対する捜査でcntを取り出すことができます。

• 必要ストレージの改善：
• 通常（ROWID）テーブルでは各wordの値を2個ずつ保存する
• WITHOUT ROWIDテーブルでは1個ずつでよい
• 検索処理の改善
• 通常（ROWID）テーブルではwordのB-TreeとROWIDのB-Tree、2回のバイナリサーチが必要
• WITHOUT ROWIDテーブルではwordのB-Treeに対する1回のバイナリサーチでよい

INTEGER PRIMARY KEY利用時にWITHOUT ROWID指定なしでも最適化される理由：

WITHOUT ROWID指定なしの通常（ROWID）テーブルで、INTEGER PRIMARY KEYを利用する場合、PRIMARY KEYに指定したカラムはROWIDのエイリアスになります。このエイリアス機能によりPRIMARY KEY用のB-TreeをROWIDとは別に構築する必要がなくなり、WITHOUT ROWIDを指定した場合と同様の効果が得られます。

参考：

• The WITHOUT ROWID Optimization

[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

[haskell][persistent][sqlite] Haskellでデータベースを利用するには

HaskellでDBの読み書きをするにはどうすればいいか？
yesodのpersistentパッケージが利用できます。本家のサイトを参考にpersistentパッケージの特徴と、簡単な処理を行うサンプルをまとめました。

persistentパッケージの特徴

• 「型安全、簡潔、宣言的」という原則に従う
• DB（PostgreSQL, SQLite, MySQL, MongoDB, etc.）に依存しないAPI
• 安全で生産的なクエリーインターフェース
• 他のプログラミング言語からのDBアクセスも考慮
• yesodに含まれているが、独立したライブラリとして利用可能

主要な型の説明

• PersistValue
• persistentの基本構成要素。DBから出し入れする値を表すデータ型。

data PersistValue = PersistText Text
                  | PersistByteString ByteString
                  | PersistInt64 Int64
                  | PersistDouble Double
                  | PersistRational Rational
                  | PersistBool Bool
                  | PersistDay Day
                  | PersistTimeOfDay TimeOfDay
                  | PersistUTCTime UTCTime
                  | PersistZonedTime ZT
                  | PersistNull
                  | PersistList [PersistValue]
                  | PersistMap [(Text, PersistValue)]
                  | PersistObjectId ByteString -- ^ intended especially for MongoDB backend

• PersistField
• リレーショナルデータベースのカラムに対応。Haskellの任意のデータ型（datatype）とPersistValueを相互にどうマーシャリングする手順を定義する。
• PersistEntity
• PersistentEntityのインスタンスはリレーショナルデータベースのテーブルに対応する。
• PersistentStore
• 各データストア（PostgreSQL, SQLite, MongoDB, etc.）はPersistentStoreのインスタンスを持つ。PersistentValueからDB固有の値への変換が行われる。
• runSqliteは引数で渡されたconnection stringを用いて、DBへのコネクションを一つ生成する。一回のrunSqliteの呼び出しは、一つのトランザクションの中で実行される。
• 対応関係
• persistentの型と、データベースの対応関係を整理すると以下のようになる。

persistent	SQL
PersistValue	データの型(VARCHAR, INTEGER, etc)
PersistField	カラム
PersistEntity	テーブル
PersistStore	データストア（PostgreSQL, SQLite, MySQL, MongoDB）

データのINSERT/SELECTサンプル

以下の処理を行うサンプルを掲載しておきます。個々の処理が何を意味しているのかはコメントを参考にしてください。

• personテーブルを生成
• レコードを一つINSERT
• SELECTで登録したレコードを取得
• 取得したレコードを表示

{-# LANGUAGE EmptyDataDecls    #-}
{-# LANGUAGE FlexibleContexts  #-}
{-# LANGUAGE GADTs             #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE QuasiQuotes       #-}
{-# LANGUAGE TemplateHaskell   #-}
{-# LANGUAGE TypeFamilies      #-}
import Control.Monad.IO.Class  (liftIO)
import Database.Persist         -- persistentパッケージ
import Database.Persist.Sqlite  -- persistent-sqliteパッケージ
import Database.Persist.TH      -- persistent-templateパッケージ

-- mkPersist
--   mkPersist :: MkPersistSettings -> [EntityDef SqlType] -> Q [Dec]
--   データ型、PersistentEntityインスタンスを生成
-- sqlSettings
--   mkPersistの挙動を変える設定値
-- mkMigrate "migrateAll"
--   マイグレーション処理を定義
-- QuansiQuotes [xx|..|] でスキーマ定義。
--   persistentLowaCaseはテーブル名・フィールド名に、"_"区切り小文字を
--   利用することを指示している。
share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Person             -- personテーブル。プライマリキーとして"id"カラム自動生成。
    name String    -- nameカラム（VARCHAR, NOT NULL制約）
    age Int Maybe  -- ageカラム（INTEGER, MaybeはNULLを許容することを意味する）
    deriving Show
|]

main :: IO ()
main = runSqlite ":memory:" $ do -- DBオープン時の引数。メモリDB利用。"test.db"等の
                                 -- ファイル名を渡すとファイルDBがオープンされる。
    -- personテーブル生成
    runMigration migrateAll

    -- name: "Michael", age: 26 のレコードを INSERT
    -- id（プライマリキー）が返値として返される。
    michaelId <- insert $ Person "Michael" $ Just 26
    -- id でレコードを SELECT。該当レコードが返される。
    michael <- get michaelId
    -- 返されたレコード（Personインスタンス）を表示。
    liftIO $ print michael

Uniqueness

大文字から始まるデータ型の宣言を追加することで、指定のカラムにUNIQUE制約を付与することができます。
以下にINSERT/SELECTサンプルをベースに、addressおよび、firstName+lastNameにUNIQUE制約を付与したサンプルコードを掲載しておきます。UNIQUE制約に関連する処理のみコメントで説明を加えています。

{-# 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

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Person
    firstName String -- nameをfistNameと
    lastName String  -- lastNameに分解
    age Int Maybe
    address String  -- addressカラム（VARCHAR）を新たに追加
    Address address -- addressカラムをUNIQUE制約に（先頭大文字でAddressを宣言）
    PersonName firstName lastName -- firstName, lastNameの組合せをUNIQUE制約に
    deriving Show
|]

main :: IO ()
main = runSqlite ":memory:" $ do
    runMigration migrateAll

    -- addressも引数に追加。bobのデータも登録してみる。
    let michaelAddress = "1-2 xx Tokyo"
    michaelId <- insert $ Person "Michael" "Snoyman" (Just 26) michaelAddress
    bobId <- insert $ Person "Bob" "Marley" (Just 29) "2-20 yyy Hokkaido"

    -- UNIQUE制約フィールドは getBy で SELECTがかけられる
    bob <- getBy $ Address "2-20 yyy Hokkaido"
    -- bob　の情報を表示。
    liftIO $ print bob

    -- PersonNameでもSELECTが可能。fistName, lastNameを渡してインスタンスを生成
    michael <- getBy $ PersonName "Michael" "Snoyman"
    liftIO $ print michael

    -- 登録されていないAddressでSELECTするとNothingが返される
    fail <- getBy $ Address "123-456 Fukuoka"
    liftIO $ print fail

    -- UNIQUE制約に違反するレコード（Michaelと重複）を登録しようとするとエラーになる
    markId <- insert $ Person "Mark" "Twain" (Just 29) michaelAddress
    -- エラー出力：
    -- uniqueness: user error (SQLite3 returned ErrorConstraint while attempting to perform step.)
    mark <- get markId
    liftIO $ print mark

参考にしたサイト

• http://www.yesodweb.com/book/persistent
• Hackage
• persistent package
• persistent-sqlite package
• persistent-template package
• Database.Persist の関数群を簡単に説明

[sqlite] sqliteの内部テーブル

sqliteにはスキーマ、データベースファイル、テーブルのメタ情報を管理するための内部テーブルが用意されています。どんなテーブルが用意されていて、どのように活用できるかをまとめておきます。

テーブル一覧：

• sqlite_master
• sqlite_temp_master
• sqlite_sequence
• sqlite_stat1
• sqlite_stat2
• sqlite_stat3

参考："The SQLite Database File Format"の2.5 Storage Of The SQL Database Schema章

sqlite_master

sqlite_masterテーブルは、データベースファイル毎に一つだけ生成されます。このテーブルの中には、該当データベースファイルの中に生成された、以下の4種類のオブジェクトの情報が格納される。

• テーブル
• インデックス
• ビュー
• トリガー

スキーマ：

CREATE TABLE sqlite_master(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

• type
• オブジェクトのタイプ。"table", "index", "trigger", "view"のいずれかが格納される。
• name
• オブジェクトの名前。typeが"table"もしくは"view"の場合はtbl_nameと一致する。
• tbl_name
• オブジェクトが所属するテーブルの名前。
• rootpage
• テーブル及びインデックスのroot b-tree pageの番号。ビュー、トリガー、バーチャルテーブルの場合は0もしくはNULLが格納される。
• sql
• オブジェクトを生成する際に実行されたSQL文（CREATE TABLE, CREATE INDEX, CREATE VIEW, CREATE TRIGGER等）が格納される。

このテーブルの利用例：

  SELECT name FROM sqlite_master
  WHERE type='table'
  ORDER BY name;

例えば上記のSQLを実行すると、DBファイル中のテーブルの一覧がテーブル名でソートした状態で出力される。

sqlite_temp_master

sqlite_temp_masterテーブルは、テンポラリデータベースにおいて、sqlite_masterテーブルの代わりに生成される。名前が異なる以外はsqlite_masterと仕様は同じ。

sqlite_sequence

sqlite_sequenceテーブルは、データベースファイル毎に一つだけ生成される。データベース内に存在しているテーブルのINTEGER PRIMARY KEYの最大値を保持する。この最大値はAUTOINCREMENTの動作で利用される。

スキーマ：

CREATE TABLE sqlite_sequence(name,seq);

sqlite_stat1

ANALYZEコマンドで生成される内部テーブル。テーブルやインデックスの補助的な情報が格納され、query plannerが効率的なクエリを見つけるために利用される。 アプリケーションからupdate, delete from, insert into, dropを実行できるが、createとalter tableは実行できない。

スキーマ：

CREATE TABLE sqlite_stat1(tbl,idx,stat);

sqlite_stat2

SQLiteのバージョンが3.6.18と3.7.8の間で、かつ、SQLITE_ENABLE_STAT2オプションでコンパイルされている場合にのみ利用される。インデックス中のキーの分散に関する補助的な情報が格納される。

スキーマ：

CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample);

sqlite_stat3

SQLiteのバージョンが3.7.9以降で、かつ、SQLITE_ENABLE_STAT3オプションでコンパイルされている場合にのみ生成・利用される。インデックス中のキーの分散に関する情報、および、query plannerがよりよいアルゴリズムを生成するための情報が格納される。

スキーマ：

CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample);