Haskellでのデータベースアクセス
PostgresqlのHDBCを利用しました。
HDBC: Haskell Database Connectivity
作業場所の作成
$ 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