YesodでPersist ODBCの利用方法(oracle)

YesodでPersistent ODBC for oracle

Persistent ODBCを利用してOracle DBへ接続する

必要なパッケージ

dev-db/unixODBC
dev-haskell/hdbc-odbc
dev-haskell/persistent-odbc
dev-db/oracle-instantclient-odbc

Oracle DBのバージョンは19cで実施

データベースの準備

Oracle DBにスキーマを準備する、いろいろなデータベースオブジェクトの言い回しがオラクルデータベースのプロからした違うかも知れません。

sqlpusで接続

$  sqlplus SYSTEM/<password>@192.168.0.xx/oracle

スキーマ作成

SQL> CREATE USER DEVLOVE IDENTIFIED BY chomechome123;

User created.

権限設定

GRANT CONNECT,RESOURCE,CREATE SEQUENCE,CREATE VIEW TO DEVLOVE;

Grant succeeded.

USERSスペースに書き込み容量設定

設定しない場合INSERTで ORA-01950: no privileges on tablespace ‘USERS’ のエラーで怒られるので注意

SQL> ALTER USER DEVLOVE QUOTA UNLIMITED ON USERS;

User altered.

作成したスキーマで再接続

接続できれば設定は完了

$ sqlplus DEVLOVE/chomechome123@192.168.0.xx/oracle

ODBCを設定する

Linuxの場合、設定ファイルに少し修正する必要がある

Driver用設定ファイル
- /etc/unixODBC/odbcinst.ini
DBC システムDSN設定ファイル
- /etc/unixODBC/odbc.ini

ファイルの一覧は odbcinst コマンドで確認できる

$ odbcinst -j
unixODBC 2.3.9
DRIVERS............: /etc/unixODBC/odbcinst.ini
SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini
FILE DATA SOURCES..: /etc/unixODBC/ODBCDataSources
USER DATA SOURCES..: /home/cuomo/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Driver用設定ファイル修正

odbcinst.ini ファイルで設定します、 libsqora.so.19.1 については oracle-instantclient 系のパッケージで入りますので、他のディストリビューションの場合、適切設定する

[Oracle19Drv]
Description     = Oracle ODBC driver for Oracle 19c
Driver          = /usr/lib64/oracle/client/lib64/libsqora.so.19.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =

ドライバーの確認は以下のとおり

$ odbcinst -q -d
[Oracle19Drv]

DSN設定ファイル

DSN(Data Source Name)の設定を行って、DNSが利用するサーバーやドライバーの関連を作成します、 Driver へ先ほど作成したドライバ名、 ServerNameは Oracle DBのインスタンスを指定する

[ORACLE19]
Driver = Oracle19Drv
DSN = ORCL
ServerName = //192.168.0.xx:1521/oracle

はかにも設定パラメータがあるが分からないので必要最低限

YesodのPersistent ODBC対応

PostgreSQLのプロジェクトを改変してODBC対応化する

$ stack new yesod-ora --resolver lts-16.20 yesodweb/postgres

修正内容は

settings.yml をODBC用の設定へ修正する
AppSettings の appDatabaseConf で設定を読めるようにする
アプリケーションが実際のコネクションプールを取得できるように修正する

settings.yml の修正する

DSNには、ODBCの設定で作成したものを指定する、UIDとPWDは Oracle DBで作成したスキーマの設定を設定する

...
database:
  odbcconn:    "_env:ORA_CONN:DSN=ORACLE19;UID=DEVLOVE;PWD=chomechome123;"
  poolsize:    "_env:ORA_POOLSIZE:10"
  dbtype:      "_env:ORA_DBTYPE:Oracle False"  # Oracle 12c over
...

AppSettings の修正

appDatabaseConf に OdbcConf を設定する

data AppSettings = AppSettings
   { appStaticDir              :: String
   ...
-    , appDatabaseConf           :: PostgresConf
+    , appDatabaseConf           :: OdbcConf

OdbcConfがFromJSONクラスのインスタンスになっていないので追加する

instance FromJSON OdbcConf where
    parseJSON = withObject "OdbcConf" $ \o -> do
      odbcconn <- o .:  "odbcconn"
      poolsize <- o .:? "poolsize" .!= (10 :: Int)
      dbtype   <- o .: "dbtype"
      let oc = OdbcConf
               { odbcConnStr = odbcconn
               , odbcPoolSize = poolsize
               , odbcDbtype = dbtype
               }
      return oc

コネクションプール接続設定修正

コネクションプール作成のためのデータベース依存の関数を差し替える

- import Database.Persist.Postgresql          (createPostgresqlPool, pgConnStr,
-                                             pgPoolSize, runSqlPool)
+ import Database.Persist.ODBC   (createODBCPool, oracle, OdbcConf(..), runSqlPool)
...

実際のプール作成コードをOdbcConfを利用した方法へ修正する

-    pool <- flip runLoggingT logFunc $ createPostgresqlPool
-        (pgConnStr  $ appDatabaseConf appSettings)
-        (pgPoolSize $ appDatabaseConf appSettings)
+    pool <- flip runLoggingT logFunc $ createODBCPool (Just oracle)
+        (odbcConnStr $ appDatabaseConf appSettings)   -- OdbcConf
+        (odbcPoolSize $ appDatabaseConf appSettings)  -- OdbcConf

modelを修正する

Yesodのscaffoldが作成する model に Userという名前のデータ型がディフォルトで作成されるが、Oracle DBで利用する場合の注意点として、userという名前のテーブル名のオブジェクトは作成出きるが問題を起こすので最初に名前を変えておく必要がある。

...
OraUser
    ident Text
    password Text Maybe
    UniqueUser ident
    deriving Typeable
OraEmail
    email Text
    userId OraUserId Maybe
    verkey Text Maybe
    UniqueEmail email
...

OraUser や OraUserId のようにすべて修正する

マイグレーション

マイグレーションについても、初回は問題なく動作するが2回め以降の動作でCONSTRAINT関連のALTERで権限系のエラーが出力される、開発用でしか利用しないと思われるので、権限を上手に設定するか停止しておいた方がよいと思う

$ stack exec -- yesod devel
...
Starting devel application
Migrating: ALTER TABLE "ora_email" ADD CONSTRAINT "ora_email_user_id_fkey" FOREIGN KEY("user_id") REFERENCES "ora_user"("id")
05/Mar/2021:16:31:03 +0900 [Debug#SQL] ALTER TABLE "ora_email" ADD CONSTRAINT "ora_email_user_id_fkey" FOREIGN KEY("user_id") REFERENCES "ora_user"("id"); []
devel.hs: SqlError {seState = "[\"HY000\"]", seNativeError = -1, seErrorMsg = "execute execute: [\"2275: [Oracle][ODBC][Ora]ORA-02275: such a referential constraint already exists in the table\\n\"]"}
...

コード

Github yesod-ora

Posted on 2021-03-06 08:47:04

魴鮄のブログ