Haskellでのデータベースアクセス

PostgresqlのHDBCを利用しました。

作業場所の作成

$ mkdir haskelldatabase
$ cd haskelldatabase

サンドボックスの作成

$ cabal sandbox init
Writing a default package environment file to
/Users/bar/work03/haskelldatabase/cabal.sandbox.config
Creating a new sandbox at
/Users/bar/work03/haskelldatabase/.cabal-sandbox

HDBCのインストール

$ cabal install hdbc
Resolving dependencies...
Notice: installing into a sandbox located at
/Users/bar/work03/haskelldatabase/.cabal-sandbox
Configuring mtl-2.2.1...
Configuring old-locale-1.0.0.7...
Downloading utf8-string-1.0.1.1...
Configuring text-1.2.2.1...
Configuring utf8-string-1.0.1.1...
Building old-locale-1.0.0.7...
Building mtl-2.2.1...
Building text-1.2.2.1...
Building utf8-string-1.0.1.1...
Installed old-locale-1.0.0.7
Configuring old-time-1.1.0.3...
Building old-time-1.1.0.3...
Installed mtl-2.2.1
Installed utf8-string-1.0.1.1
Installed old-time-1.1.0.3
Installed text-1.2.2.1
Downloading convertible-1.1.1.0...
Configuring convertible-1.1.1.0...
Building convertible-1.1.1.0...
Installed convertible-1.1.1.0
Downloading HDBC-2.4.0.1...
Configuring HDBC-2.4.0.1...
Building HDBC-2.4.0.1...
Installed HDBC-2.4.0.1
Updating documentation index
/Users/bar/work03/haskelldatabase/.cabal-sandbox/share/doc/x86_64-osx-ghc-8.0.1/index.html

postgresqlドライバのインストール

$ cabal install hdbc-postgresql
Resolving dependencies...
Notice: installing into a sandbox located at
/Users/bar/work03/haskelldatabase/.cabal-sandbox
Configuring parsec-3.1.11...
Building parsec-3.1.11...
Installed parsec-3.1.11
Downloading HDBC-postgresql-2.3.2.4...
Configuring HDBC-postgresql-2.3.2.4...
Building HDBC-postgresql-2.3.2.4...
Installed HDBC-postgresql-2.3.2.4
Updating documentation index
/Users/bar/work03/haskelldatabase/.cabal-sandbox/share/doc/x86_64-osx-ghc-8.0.1/index.html
$

データベースの準備

$ createuser -a -d -U bar -P
user03
Enter password for new role: 
Enter it again: 
$ createdb -E UTF8 -O user03 -U user03
testdb03

データベスの確認

$ psql -l
                                     List of databases
   Name    |   Owner    | Encoding |   Collate   |    Ctype    |     Access privileges     
-----------+------------+----------+-------------+-------------+---------------------------
 testdb03  | user03     | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |

テーブルの作成

$ psql -d testdb03
psql (9.6.1)
Type "help" for help.

testdb03=# 
testdb03=# create table foo(name varchar(50));


testdb03=# \dt;
         List of relations
 Schema | Name | Type  |   Owner    
--------+------+-------+------------
 public | foo  | table | bar
(1 row)

テーブル情報の確認

> :t connectPostgreSQL
connectPostgreSQL :: String -> IO Connection
> :t getTables 
getTables :: IConnection conn => conn -> IO [String]
> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= (\x -> getTables x)
["foo"]
>

データ挿入、オートコミットはされない。

> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= (\x -> run x "insert into foo values('abc')" [])
1
>

データ挿入＋コミット

> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= (\x -> run x "insert into foo values('ghi')" [] >>= (\_ -> commit x))
>

検索(prepare -> execute -> fetch->結果はMaybeというパターン)

> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= \x -> prepare x "select * from foo" >>= (\y -> execute y [] >>= \_ -> fetchRow y)
Just [SqlByteString "abc"]

列名付き検索

> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= \x -> prepare x "select * from foo" >>= (\y -> execute y [] >>= \_ -> fetchRowAL y)
Just [("name",SqlByteString "abc")]

列名付き検索をMapで取得

> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= \x -> prepare x "select * from foo" >>= (\y -> execute y [] >>= \_ -> fetchRowMap y)
Just (fromList [("name",SqlByteString "abc")])

複数行取得

> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= \x -> prepare x "select * from foo" >>= (\y -> execute y [] >>= \_ -> fetchAllRows y)
[[SqlByteString "abc"],[SqlByteString "def"],[SqlByteString "ghi"]]

検索結果をMapで取得

> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= \x -> prepare x "select * from foo" >>= (\y -> execute y [] >>= \_ -> fetchAllRowsMap y)
[fromList [("name",SqlByteString "abc")],fromList [("name",SqlByteString "def")],fromList [("name",SqlByteString "ghi")]]

日本語の取り扱い

> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= (\x -> run x "insert into foo values('あいうえお')" [] >>= (\_ -> commit x))
> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= \x -> prepare x "select * from foo" >>= (\y -> execute y [] >>= \_ -> fetchAllRowsMap y)
[fromList [("name",SqlByteString "abc")],fromList [("name",SqlByteString "def")],fromList [("name",SqlByteString "ghi")],fromList [("name",SqlByteString "\227\129\130\227\129\132\227\129\134\227\129\136\227\129\138")]]

NULLの取り扱い SqlNullというデータがある

> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= \x -> prepare x "select * from foo where name is null" >>= (\y -> execute y [] >>= \_ -> fetchAllRowsMap y)
[fromList [("name",SqlNull)]]

トランザクションを使う

> connectPostgreSQL "host=localhost dbname=testdb03 user=user03" >>= \x -> withTransaction x (\y -> run y "insert into foo values('klm')" [])
1

do記法

import Database.HDBC 
import Database.HDBC.PostgreSQL

main = do 
  con <- connectPostgreSQL "host=localhost dbname=testdb03 user=user03"
  run con "insert into foo values('qwerty')" []
  commit con

IT練習ノート

IT関連で調べたこと（実際は嵌ったこと）を書いています。

Haskellでのデータベースアクセス