PHPのIntlDateFormater, 大晦日にいきなり来年になる

PHPにIntlDateFormaterというクラスがありますが、これを使って「今日」を生成している箇所で、本日2023年大晦日にもかかわらず、2024-12-31と出てきました。以下のSandboxにコードを貼っておきます。これはやっぱりバグでしょうね。DateTimeのformatならきちんと2023と出ています。

https://onlinephp.io/c/1cbcf

(追記)バグレポートをしたら、フォーマット文字列はYではなく、yかyyyyを使えと教えてもらいました。なるほど、Yの意味(year of “Week of Year”)がやっとわかった。

FileMakerからPowerShellスクリプト実行

FileMakerのスクリプト内でPowerShellを動かしたくなったのですが、概ねうまく行ったのでノウハウを忘れないうちにブログにしておきます。もちろん、Windows版です。PowerShellで動かしたいのは画面ショットです。こちらのサイト「Windowsのコマンドラインからスクリーンショットを撮る(PowerShell)」にそのまま使えるスクリプトがあったので使わせてもらいました。ありがとうございます。また、FileMakerのスクリプトからシェルスクリプトを稼働する方法は、「Claris Community | Powershell script not working」に記載があります。

これで終わるかといえば、それだけならブックマークに忘れないように覚えさせておくだけで終わってしまいます。まず、通常は、Windowsでは、いきなりシェルスクリプトはセキュリティ的な理由で動かせないようになっています。そこで、以下のようなコマンドを、PowerShell等で入れておく必要があります。これで実行権限が与えられます。そのユーザでログインしている間は再起動してもシェルスクリプトは動きます。「Bypass」はなんでも動かすためのものなので、オンラインからの素材は検疫(デジタル署名の確認を)する「RemoteSigned」という設定の方が少しはマシかもしれません。

Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope CurrentUser

これが実行されていないと全くスクリプトは動きません。

続いて、前述のClaris Communityのやり取りにもあるのですが、Quote関数を使うことが重要です。スクリプトを実行するためには、「イベントを送信」スクリプトステップを利用し、「送信イベント」は「ファイル/アプリケーションを開く」を選択しておきます。この選択肢は、FileMakerをWindows版で動かさないと出てきません。そして、「計算」を選択して、コマンドを送り込みます。ここで、あるシェルスクリプトにファイル名の引数を付与してPowerShellで実行する場合はこのような式になるかと思います。変数に期待する値などの状況も含めて書きます。

仮定
$scriptPath ← "C:/Users/msyk/myscript.ps1" // シェスルクリプトへの絶対パス
$targetPath ← "C:/Users/msyk/data.bmp" // スクリプトの引数に渡すファイルパス

「イベントを送信」スクリプトステップの「計算」の式:
"powershell.exe -command " & 
Quote(Quote($scriptPath) & " " &Quote($targetPath))

-commandパラメータの引数は、単一の文字列で「コマンドライン」を指定します。ですが、コマンド中に”があると、そこでパラメータが終わっているとシェルは思ってしまうので、全体をQuoteで囲みます。Quoteは全体をQuoteで囲むだけでなく、文字列内部の”は、\”のようにエスケープします。ただ、元のコマンド自体もパスであり、スペースが入る可能性を考えると、やっぱりQuoteします。コマンドラインとしてうまく値を引き渡すには、このQuoteのQuoteで大体うまくいくことがわかりました。

まず、ここで、Get関数を使えやと思うところかと思いますが、FileMakerのGet (ドキュメント)等は、「/C:/Users/msyk/Documents/」のように、頭に/がついてしまいますが、それだとPowerShellは正しくパスとして認識しません。C:/Users/…のように記述されていないといけないようです。ということで、Get関数の結果を文字列処理するか、いっそのこと、絶対パスを文字列で書くか、まあ、その辺りはソリューションに応じて頑張ってください。

もう1つ、ファイル名に使ってはいけない文字列があることを忘れないでください(例えば、こちらのサイトで一覧されています)。|や/はファイル名としては使えません。これらは、Substitute関数で置き換えてから、PowerShell実行スクリプトの引数に与えます。また、ファイル名として使えない名前(COM1など)もあるので、何かで取得した名前をファイル名として利用する場合にはデータを俯瞰して対策は必要になります。

SimpleSAMLphp Ver.2を使ってみる(3)

(1)はIdPの起動、(2)はIdPの管理画面のチェックと進みました。ということで続いてSPです。ここでのSPはINTER-Mediatorで稼働しているという前提で話をします。状況としては次のようなものです。

  • 証明書を発行済みのドメインdemo.inter-mediator.com内で稼働する。DocumentRootは/var/www/demo_im_com
  • DocumentRootにsaml-trialディレクトリを作り、そこに、ページファイルchat.html、定義ファイルchat.phpを定義した
  • INTER-Mediatorは、saml-trial/lib/srcにgit cloneでインストールして、composerで必要なライブラリをインストール
  • 結果的に、SimpleSAMLphpのレポジトリのルートは、DocumenRoot以下、saml-trial/lib/src/INTER-Mediator/vendor/simplesamlphp/simplesamlphp となる
  • 設定ファイルのparams.phpは、saml-trial/lib/src/params.phpとする
  • demo.inter-mediator.comをホストしているApache2のsiteファイルでは、以下のように、/simplesamlへのエイリアスを作成する(Aliasの行は1行で記述)
<VirtualHost *:443>
    ServerAdmin info@inter-mediator.org
    DocumentRoot /var/www/demo_im_com
    ServerName demo.inter-mediator.com
Alias /simplesaml "/var/www/demo_im_com/saml-trial/lib/src/INTER-Mediator/vendor/simplesamlphp/simplesamlphp/public"

いきなり動くかなと確かめてみたら、ダメでした。composerの扱いをちゃんとやらないといけません。ここでは、composer.jsonのsimplesamlphp/simplesamlphpの値を”2.0.4″とバージョンをしっかり入れるようにしてみました。INTER-Mediatorの場合、composer clearnでライブラリを消して、composer update, composer installの順でコマンドを入れれば良いでしょう。

simplesamlphpの管理ページは、前回にも紹介したように、赤いヘッダなどがついたもので、CSSやスタイルシート、画像などが提供されています。ブラウザでパスを見る限りは、/simplesaml/assets/base…となっているので、レポジトリのpulic/assetsを見るのですが、空です。どうやら、assets以下の内容は、simplesamlphp/simplesamlphp-assets-baseという別のパッケージにあるようで、これが読み込まれていません。この別パッケージをassets以下に展開するには、composer installが必要なようで、結果的にupdateとinstallは両方行う必要があるようです。

設定ファイルの記述

これまでのセットアップを行うと、SimpleSAMLphpのSP自体は、パスがちょっと長いですが、/var/www/demo_im_com/saml-trial/lib/src/INTER-Mediator/vendor/simplesamlphp/simplesamlphpに存在することになります。以下のこのパスを「SPのルート」と記載します。このディレクトリの、configに設定ファイル、metadataにメタデータファイル、certに証明書類を入れるのが基本です。以下、参考にコマンドを記述しますが、INTER-Mediatorではもう少し手軽にする方法を用意していて、近々、これをSimpleSAMLphp Ver.2向けに更新する予定です。

まず、通信暗号化のための証明書を作ります。この証明書はサイトのTLSのための証明書を使ってもよく、実際には案件ではそのようにしましたが、SimpleSAMLphpのサイトの説明では、10年期限の自己署名証明書を作っています。サイトの証明書はつまり「自己署名だとダメかも」と思って使っていたわけですが、本家の説明がいきなり自己署名なので、単に暗号化のためだけに使っているということですね。opensslコマンドの後に属性などを入力しますが、(1)のIdPのところと同様適当に入れればいいかと思います。-outと-keyoutの後のファイル名も適当に指定します。

cd cert
openssl req -newkey rsa:3072 -new -x509 -days 3652 -nodes -out sp.crt -keyout sp.pem

SPのルート以下、configディレクトリには、元からあるconfig.php.distからコピーしたconfig.phpを用意します。そして、その内容を変更します。ポイントは以下の点です。baseurlpathは、SPのルートのpublicを参照するようにします。以前はwwwを参照していましたが、Ver.2で変わっています。残り3つの設定は、IdPと同様ですので、(1)の記事を参照してください。

'baseurlpath' => 'saml-trial/lib/src/INTER-Mediator/vendor/simplesamlphp/simplesamlphp/public/',
'technicalcontact_email' => 'your_email',
'secretsalt' => 'your_salt',
'auth.adminpassword' => 'your_admin_pass',

SPのルート以下、configディレクトリには、元からあるauthsources.php.distからコピーしたauthsources.phpを用意します。以下のように、default-spキーの配列の要素に、certificateとprivatekeyのエントリーを用意して、ここで作成したキーファイルと証明書ファイルを指定します。そして、entityIDをサイトのドメインに設定しておきます。

'default-sp' => [
  'saml:SP',
  'certificate' => 'sp.crt',
  'privatekey' => 'sp.pem',

   // The entity ID of this SP.
   'entityID' => 'https://demo.inter-mediator.com/',
   :

SPのルート以下、metadataディレクトリには、元からあるsaml20-idp-remote.php.distからコピーしたsaml20-idp-remote.phpを用意します。このファイルの最後(とはいえ、中身は短いコメントがあるのみ)に、IdPの管理ページからコピーした配列をコピーしておきます。

SPの管理ページからメタデータを取得

ということで、インストールに少しハマってしまいましたが、なんとか動きました。一応のルートは、https://demo.inter-mediator.com/simplesaml ですが、こちらは「ようこそ」と出るだけです。SPの管理ページに行くには、このURLの後にadminをつけた、https://demo.inter-mediator.com/simplesaml/admin にアクセスします。そして、config.phpで指定したパスワードを入力して、管理者として認証します。

設定のページは諸々確認できますが、ModulesのところでIdPとしては稼働していないことなどが分かります。

Testのタブでdefault-spのリンクをクリックすると、次のような画面が見えており、登録したIdPを認識していることが分かります。ただ、ここで「選択」をクリックするとエラーになるので、まだ何か問題なのかもしれません。

連携のところで、「V」の部分をクリックすると、メタデータが表示されます。このメタデータを、IdPに登録します。IdPがSimpleSAMLphpなら、metadata/saml20-sp-remote.phpファイルに追記することになります。

認証できています

それでは実際にIdPで認証したユーザで、INTER-Mediatorのアプリケーションを使ってみます。通常、ログインパネルが出てくるとこが、IdPというか、SPの画面に行きます。ここでは、まず、IdPを選択します。

すると、ログインパネルが出てきます。こちらはドメインを見ればわかるように、IdP側に切り替わっています。ここでは、テストユーザのuser01でログんを試みます。

無事にログインができ、メッセージが見えています。

ちなみに、SAML-tracerを使って追っかけてみました。チャットのアプリケーションのURL(https://demo.inter-mediator.com/saml-trial/chat.html)をブラウザに入れると、何度かリダイレクトされて、IdPの側の認証ダイアログが表示されます。そこまでのトレースは以下の通りです。

続いて、正しいユーザとパスワードを入力して、IdPにポストしますが、その後、アプリケーションのURLにリダイレクトされています。この時は、認証が通っているので、アプリケーション側でも、認証が通った後の処理をして、ページが構築されています。

ということで、SimpleSAMLphp Ver.2.0.4でも動くことを確認しましたが、途中ちょっとハマった理由は、すでにVer.3の作業に入っていることに気づかず、dev-masterで作業したら、色々思った通りに動かなかったのでした。Packagistのサイトを見て、あ、Ver.3.0.0になっていると気づき、Ver.2.0.4で通るようにやり直して稼働を確認できたという次第です。ちゃんとチェックしようねってことですね。

SimpleSAMLphp Ver.2を使ってみる(2)

前の記事では、テスト用のIdPを起動するところまでを説明しました。Ver.2ではIdPの管理画面も新しくなっているので、続いてその管理画面に何が出ているかを確認しましょう。

まず、画面上部のタブ「設定」のページです。最初にSimpleSAMLphpのインストール場所やバージョンが見えています。正しく、Ver.2.0.4がインストールされていると判断できるでしょう。そして、インストールされているモジュールや動作チェックなどがあります。Ver.2になった変更点として、プラグイン的に必要な機能は追加するようになったと記載があり、必要な素材が全部入っている状態ではありません。必要な機能があるのなどはこの画面などでのチェックも必要かもしれません。

前の画面のDetailsにある「Information on your PHP installation」のリンクは、phpinfo()関数を動かした結果を表示します。「ホストネームやポート、プロトコルを診断」は次のような画面を表示します。サーバがきちんと動くようなら、特に確認は不要かもしれません。

「Test」のタブでは、admin、default-sp、example-userpassの3つのリンクがあります。まず、adminは次のように、管理者ログインに関する情報が見えています。

「default-sp」をクリックしても「No identity providers found. Cannot continue.」と出るだけです。これは正しい状態なのか、追々調べます。

example-userpassをクリックすると、次のようにログインパネルが出て、ログインの検証が可能です。ここで、config/authsource.phpで定義したユーザとパスワードを入れてログインをしてみます。

正しいユーザ名とパスワードを入れれば認証が行われて、その時に得られる属性についても表示されます。

ページ上部の「連携」をクリックすると、次のような表示が見えます。SPが2つになってしまっていますが、idpのドメイン名を設定した側を利用するものとして想定します。ここでは、中央付近に見えているボックスの下部にある「V」部分をクリックします。

V部分をクリックすると、表示が開いて、IdPのメタデータが表示されます。上部が一般的なXMLによる記述で、下部がSimpleSAMLphpで利用できるPHPの配列形式のメタデータです。ともかく、SPとの連携の時のデータは取り出しができるようです。

以上のように、IdPの管理画面としては、以前より少しは機能が増えたものの、SPの登録などはないようなので、やはり基本は設定ファイルを修正するということになるでしょう?認証可能かどうかやインストール状態などの動作チェック等にはある程度は利用できそうです。

SimpleSAMLphp Ver.2を使ってみる(1)

PHPでSAML認証を実現するライブラリ、SimpleSAMLphpが、2023年からVer.2となりました。SAML 2.0に対応するのは以前から、つまり、SimpleSAMLphp Ver.1でもSAML 2.0に対応していましたが、どちらのバージョンも「2」になったということです。バージョン記述がややこしいですが、まあ、これを読んでいる方は慣れているかと思いますので、先に進みます。

(追記)ただし、SimpleSAMLphp Ver.2のIdPとSPを構築したのではありますが、どうもうまく動かないようです。とりあえず、失敗の記録として残しておきます。このシリーズは(3)まであります。

(さらに追記)手を入れつつ、文書自体を直しています。

INTER-MediatorはSimpleSAMLphpベースでSAML対応しています(勉強会での発表ビデオはこちらです)。SAMLというか、Shibboleth認証の案件を実際に行ったこともあります。ということで、SimpleSAMLphp Ver.2は早めにチェックしようと思いつつ、今になってしまいました。

SimpleSAMLphp Ver.2になっての違いはこちらのページに記載されています。かいつまんで説明すると、Shibboleth 1.3、SAML 1.1にはもう対応しないということで、SAML 2.0のみ対応となっています。ということは、Shibboleth案件は、Ver.1.19.xあたりで作業する必要があるということになります。設定ファイル名は変わっていないものの、「作り直したほうがいい」となっていますので、手順を含めて、引き続いてそのあたりは説明したいと思います。それから、いくつかの重要なパスも変わっています。これも説明で紹介します。

INTER-MediatorのSAMLのテストは、SimpleSAMLphpによるIdPと、SimpleSAMLphpによるSPを使って行うようにセットアップをしてあるのですが、改めて、この環境を構築し直しを始めました。その記録をブログにつけていこうと思います。IdPには、テスト用のアカウントをいくつか記録する程度で、そこから別の認証サービスを使うまではとりあえずは考えていません。

テスト環境ですが、Ubuntu Server 22..0.4 LTSです。よって、PHPは8.1です。普通に、Apache2、PHPとモジュールをインストールしました。INTER-Mediatorをインストールする以外には、PHPのSOAPモジュールを追加するだけで大丈夫でした。 テスト用のアプリケーションも当然ながらINTER-Mediatorで作ってあるのですが、SimpleSAMLphpのVer.1とVer.2の相互運用も考えないといけないのかなとも考えられます。つまり、Shibbolethに対応させるという案件も今後ある可能性もあるわけですね。

さて、数年前に一生懸命検証をした時の1つの結論は、「ちゃんとドメインを切って、正しい証明書をセットアップしたサイト」にするということです。その時の設定はまだあって、IdP用にidp.inter-mediator.com、アプリケーションとSPはdemo.inter-mediator.com/saml-trialにしました。いずれも、Let’s Encryptではありますが、それぞれ有効な証明書が動き、通信はすべてHTTPSで動くという状態になっています。

IdPサイトの構築

IdPのサイトは、SimpleSAMLphpのコードをそのまま使って構築します。Ubuntuなので、/var/www以下に、例えば、以下ようなコマンドで、コードを取り出します。バージョンごとにタグがあるので、Ver.2系列の最新版である2.0.4をインストールすることにします。そして、composerを動かして、必要なライブラリのインストールを行います。

cd /var/www
git clone https://github.com/simplesamlphp/simplesamlphp simplesaml-idp
cd simplesaml-idp
git checkout v2.0.4
composer update

/var/www以下は、ログインしたユーザであれば書き込みできるという前提で説明をします。また、ログインしたユーザはsudoコマンド可能であって、root権限が必要な処理はsudoを利用するという方針でコマンドを示します。

このコマンドで、/var/www/simplesaml-idpというディレクトリができ、そこにレポジトリの内容が展開されました。このディレクトリを公開するのかというと、そうではなくて、この中のpublicを公開します。以前はwwwというディレクトリでしたが、Ver.2でpublicという名前に変えたそうです。ということで、Apache2のidp.inter-mediator.comのサイト設定ファイルは、大体以下のような記述つまり、DocumentRootがある感じです(実際には証明書の設定などもあってもっとややこしい)。/simplesamlはIdPの設定ファイルに書かれているbaseurlpathの値でもあるので、とりあえずAliasを定義しておきます。

<VirtualHost *:443>
    ServerAdmin info@inter-mediator.org
    DocumentRoot /var/www/simplesaml-idp/public
    ServerName idp.inter-mediator.com
    Alias /simplesaml "/var/www/simplesaml-idp/public"
:

さて、サーバを見てみましょう!という感じで開くと、次の通りです。当然、セットアップを何もしていないので、そのような表示が出るだけです。ちゃんと、設定ファイルがないとメッセージが出ています。

IdPが使う証明書を用意する

SAMLでは通信の暗号化のために証明書を使います。IdPで使用する証明書は、opensslコマンドを使って作成しますが、レポジトリのcertディレクトリに作るのが一番手軽です。このディレクトリに作った証明書関連のファイルは、フルパスを指定する必要がありません。例えば、以下のようなコマンドで作成できます。

cd /var/www/simplesaml-idp/cert
openssl req -newkey rsa:3072 -new -x509 -days 3652 -nodes \
    -out idp.inter-mediator.com.crt -keyout idp.inter-mediator.com.pem

コマンド例ではカレントディレクトリを明示するためにcdコマンドを随所で書くようにしますが、もちろん、コマンドの理解がある方は自分の状況に応じてコマンドを入れてください。そして、opensslコマンドの-outと-keyoutの2つのパラメータは実際に保存されるファイル名になるので、自分のドメイン等に変えるか、server.cert、privatekey.pemみたいな名前にするのが良いでしょう。

乱数生成などの後、入力を促されます。要するに大雑把な住所と組織などを入力します。以下は私が入力した例ですが、もちろん、ご自分の状況に合わせてください。Common Nameについては、FQDNを入れるのが良いと思われます。

Country Name (2 letter code) [AU]:JP
State or Province Name (full name) [Some-State]:Saitama
Locality Name (eg, city) []:Midori-ward
Organization Name (eg, company) [Internet Widgits Pty Ltd]:INTER-Mediator
Organizational Unit Name (eg, section) []:Authentication Support
Common Name (e.g. server FQDN or YOUR name) []:idp.inter-mediator.com
Email Address []:nii@msyk.net

なお、生成されたキーファイルは、ownerだけが読み書きできて、gropuやeveryoneに対する読み出し権限すらありません。Apache2のプロセスのユーザ(Ubuntuではwww-data)が読み出し権限があるようにします。ちょっと微妙ながら、例えば、こんなコマンドで対処します。(2024-2-21追記)アクセス権は、レポジトリの内容全体に設定した方が手軽でしょう。記事の最後に記載しました。

cd /var/www/simplesaml-idp/cert
sudo chmod a+r *

configディレクトリの設定を行う

それでは、設定を進めましょう。まず、レポジトリのルートにあるconfigディレクトリの中身です。このファイルには3つの設定ファイルを作りますが、そのうち、config.phpとauthsources.phpの2つのファイルを用意します。このファイルはスクラッチから作るのではなく、ファイル名に.distが付いたテンプレートのファイルがあるので、それをコピーして用意します。まず、ファイルをコピーします。

cd /var/www/simplesaml-idp/config
cp authsources.php.dist authsources.php
cp config.php.dist config.php

config.phpファイルは、以下のポイントを修正します。 まず、technicalcontact*は、このサーバの管理者です。基本的には自分を指定すれば良いでしょう。

:
    'technicalcontact_name' => 'Administrator',
    'technicalcontact_email' => 'msyk@msyk.net',
:
    'secretsalt' => 'whr5p645s3ig7nm9wxibfckllmjfvjl6',
:
    'auth.adminpassword' => 'samltest5682',
:
    'enable.saml20-idp' => true,
    'enable.adfs-idp' => false,
:
    'module.enable' => [
        'exampleauth' => true,
        'core' => true,
        'admin' => true,
        'saml' => true
    ],

secretsaltとauth.adminpasswordは、IdPのログインする管理者の認証関係のもので、後者がパスワード、前者が保存のためのソルトです。ソルトは、ファイルのコメントにあるように以下のコマンドで生成して、それをコピペしておきます。そのままコピーして、コマンドラインにペーストしてリターンでOKです。

LC_ALL=C tr -c -d '0123456789abcdefghijklmnopqrstuvwxyz' </dev/urandom | dd bs=32 count=1 2>/dev/null;echo

enable.saml20-idpは、文字通り、IdPの機能をアクティブにします。module.enableは、exampleauthの値をtrueにしますが、これは、設定ファイルで認証ユーザを提供する仕組みをオンにします。もちろん、簡易的にテストができるようにということです。

続いて、config/authsources.phpの修正です。まず、default-sp以下の配列において、entityIDを変更します。そして、この配列内に、privatekeyとcerificateというキーで、それぞれ秘密鍵と証明書のファイル名を指定しておきます。もちろん、ここでは、前の手順でopensslで生成したファイルを指定します。さらに、テスト用のユーザとして、example-userpassの部分のコメントを外して、その中に定義します。以下の例では、user01というユーザとuser02というユーザが定義されており、それぞれ、パスワードはuser01pass、user02passです。キーになっている’user01:user01pass’の部分でユーザ名とパスワードを表現しており、対応する配列は応答する情報を記載します。ちなみに、大学のディレクトリなどでは、eduPersonAffiliationといった属性が入ってきて、それに応じて大学生か、職員かを判断するようなロジックを求められることはよくあるようです。

:
    'default-sp' => [
        'saml:SP',

        // The entity ID of this SP.
        'entityID' => 'https://idp.inter-mediator.com/',
:
        'proxymode.passAuthnContextClassRef' => false,

        'privatekey' => 'idp.inter-mediator.com.pem',
        'certificate' => 'idp.inter-mediator.com.crt',
:
    'example-userpass' => [
        'exampleauth:UserPass',
:
        'user01:user01pass' => [
            'uid' => ['user01'],
            'eduPersonAffiliation' => ['member', 'student'],
        ],
        'user02:user02pass' => [
            'uid' => ['user02'],
            'eduPersonAffiliation' => ['member', 'employee'],
        ],
    ],

metadataディレクトリの設定を行う

続いて、レポジトリルートにあるmetadataディレクトリの設定を行います。このディレクトリも設定ファイルはないものの、拡張子が.distとなっているそれぞれのファイルのテンプレートがあるので、それをコピーして変更して利用します。3つのファイルがありますが、利用するのは2つだけです。コピーしないsaml20-idp-remote.phpファイルは、SPで利用するものです。

cd /var/www/simplesaml-idp/metadata
cp saml20-idp-hosted.php.dist saml20-idp-hosted.php
cp saml20-sp-remote.php.dist saml20-sp-remote.php

ちなみに、ファイル名がややこしいと思われるかもしれませんが、それぞれ、IdPの設定、SPの設定を行います。IdP自分自身についてはhostedの方で設定します。そして、SPの設定は自分ではないので、remoteであるということです。ファイル名にはきちんと意味があると思えば、少しは見通しよく見えるのではないでしょうか。

metadata/saml20-idp-hosted.phpについては、以下を修正します。まず、$metadata配列のキーについてはキーの値を既定値から変更して設定します。ここでは、とりあえず、IdPのドメインにしました。ちなみに、このキーを既定値のままにすると、動作がおかしかったので、これを切り替えるのが必要ではないかと思われます。そして、privatekeyとcertificateキーのファイル名を、生成したファイルのものに切り替えておきます。

$metadata['https://idp.inter-mediator.com/'] = [
    /*
     * The hostname of the server (VHOST) that will use this SAML entity.
     *
     * Can be '__DEFAULT__', to use this entry by default.
     */
    'host' => '__DEFAULT__',

    // X.509 key and certificate. Relative to the cert directory.
    'privatekey' => 'idp.inter-mediator.com.pem',
    'certificate' => 'idp.inter-mediator.com.crt',

実際にはもっといろいろ変更は必要なのでしょうけど、ここまでの設定だと、証明書やキーのファイルの整合、IdPを稼働、テストユーザの登録程度のことです。

管理ツールを稼働する

ここで、https://idp.inter-mediator.com/ つまり、Webのルートにアクセスすると次のような画面が出てきます。ちゃんと動いている模様ですが、肝心の管理作業ができません。

管理作業をするには、https://idp.inter-mediator.com/admin にアクセスします。いろいろリダイレクトしますが、認証画面が出てきます。ここでは、ユーザ名はadmin、パスワードは、config.phpファイルに指定したパスワードを入力して認証します。

最初は、以下のようにTestというタブのページになります。ここから先は次の記事で説明ます。

(2024-2-21追記)その後、v2.1.3というバージョンで試してみました。試行錯誤もありますが、まずは、全部ファイルをセットアップした後、simplesamlphpのファイルの所有者を、Webサーバのwww-dataに変更しておくのがいいように思います。例えば、次のようなコマンドです。

sudo chown -R www-data /var/www/simplesaml-idp

そして、稼働させても、次のようなエラーが出るようです。

このエラーはよく読むと、意味がわかります。どうやら、既定値では、/var/cache/simplesamlphp以下のキャッシュファイルを作るようで、そのディレクトリが必要ということに加えて、アクセス権も設定が必要なようです。例えば、次のようなコマンドで対処できます。

sudo mkdir -p /var/cache/simplesamlphp
sudo chown -R www-data /var/cache/simplesamlphp

キャッシュとして、かなりたくさんのファイルが作られます。

Ubuntu 22でINTER-Mediatorを稼働する

Ubuntu Server 22.04.1 LTS上で、INTER-Mediatorのサンプルを、MySQLで動かすところまでのセットアップ方法を紹介します。サーバは普通にDVD等でインストールします。ほぼ、デフォルトでセットアップした状態を想定しているので、Minimalの方ではありません。また、サーバアプリケーションは、SSH Serverだけをセットアップ時に含めているとします。

ということで、早速、インストール後のコマンド入力です。一気にまとめて紹介します。

sudo apt -y update
sudo apt -y upgrade
sudo apt install -y apache2 php mysql-server
sudo apt install -y php-curl php-xml php-gd libicu-dev \
                    mysql-client php-pdo-mysql
sudo apt install -y nodejs
sudo apt install -y composer
sudo chmod -R g+w /var/www
sudo chown -R www-data:<user> /var/www
sudo systemctl restart apache2

cd /var/www/html
git clone https://github.com/INTER-Mediator/INTER-Mediator.git
cd INTER-Mediator/
composer update
cd dist-docs
sudo mysql -uroot < sample_schema_mysql.sql 

「php」でインストールすると、Ver.8.1がセットアップされます。モジュール類も以前よりも多く初期設定で入っているので、記載した、php-curlなど3つと、データベースのドライバを追加するだけで大丈夫です。ただ、intlモジュールが利用するlibicu-devを入れておかないといけないのは以前から変わっていないところです。php-pdo-mysqlは実は存在しておらず、php8.1-mysqlが代わりにインストールされます。php-mysqlというモジュールもあってこちらでも良さそうな気がしますが、とりあえず、PDO本体は入るけどもMySQLのPDOサポート部分は追加しないといけないというところがポイントです。よって、PostgreSQL等でも同様にPDOドライバを入れないといけないということです。

Node.jsは「念の為に」入れておきます。composerもaptでインストールできるようになっています。

Apache2は以前の通り、www-dataユーザで稼働するので、このユーザのホームである/var/wwwのアクセス権を設定しておきますが、chownでのグループはログインユーザ名にしておくのがいいかと思います。そして、Apache2を再起動します。以前よりだいぶんとシンプルになった気がします。

後半は、INTER-Mediatorのインストールです。とりあえず、Web公開ディレクトリにレポジトリの中身を展開してそれを動かすことにします。クローン後、composer updateコマンドを動かし、サンプルのデータベースをMySQLに読み込ませて準備するだけです。これで、「http://ホストIP/INTER-Mediator/samples/」で、サンプルの目次ページが出てくるはずです。

現在は既定値でサービスサーバを落としていますが、INTER-Mediator/params.phpの以下の部分を修正すると、サービスサーバが稼働します。コード部分は修正前ですので、コメントに従って変更してみてください。Sample_formフォルダのサンプルがクライアント間同期の仕組みを組み込んであります。サンプルの目次ページだと、「Any Kinds of Samples」の最初にある「Master-Detail Style Page」のリンクを利用してください。

$notUseServiceServer = true; // 値をfalseにする
/*  // この行を消してコメントでなくする
$activateClientService = false; // 値をtrueにする
$serviceServerProtocol = "ws";
$serviceServerHost = "";
$serviceServerPort = "11478";
$serviceServerKey = "";
$serviceServerCert = "";
$serviceServerCA = "";
$serviceServerConnect = "http://localhost"; // localhostを実際のホストにする
$stopSSEveryQuit = false;
$bootWithInstalledNode = false;
$preventSSAutoBoot = false;
$foreverLog = '/tmp/forever.log';
*/ // この行を消してコメントでなくする

macOSでPHPのバージョン管理

普段、最新のPHPでの検証が多いのですが、久々に、古いアプリケーションのメンテのために、8.2.0がカレントバージョンの今、7.4を使う必要が出ました。そのアプリが8.2で動いてくれればいいのですが、Warningが出るので7.4にしたいですね。当然、phpはhomebrewを使っているのですが、標準ではカレントバージョンしかサポートしていないらしく、あちこちに掲載されている「brew install php@7.4」がエラーになって動かないのです。こんな感じ。

% brew install php@7.4
Error: php@7.4 has been disabled because it is a versioned formula!

こういう場合は、上記2行目のエラーメッセージを、そのままGoogle検索窓にコピペして検索します。やはり、StackoverflowにError: php@7.3 has been disabled because it is a versioned formulaという記事が見つかりました。

ということで、標準以外のバージョンをインストールできるTapがあるということで、以下のようにコマンドを入れれば、無事にphp 7.4がカレントになりました。

brew tap shivammathur/php
brew install shivammathur/php/php@7.4

この後に、brew link …とすればいいかと思うのですが、現行バージョンのver.8.2をunlinkする前だと、–overwriteをつけろとメッセージが出てきます。以下の流れだと、–overwriteは不要かもしれませんが、エラーの時には試してみましょう。

% brew unlink php@8.2
% brew link --overwrite php@7.4
Linking /usr/local/Cellar/php@7.4/7.4.33... 25 symlinks created.

If you need to have this software first in your PATH instead consider running:
  echo 'export PATH="/usr/local/opt/php@7.4/bin:$PATH"' >> ~/.zshrc
  echo 'export PATH="/usr/local/opt/php@7.4/sbin:$PATH"' >> ~/.zshrc
% php -v                       
PHP 7.4.33 (cli) (built: Dec  8 2022 21:39:37) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.33, Copyright (c), by Zend Technologies

そういうわけで、PHPの各バージョンは、こちらのレポジトリのようにしっかり古いものからより新しいものまでキープしてくれていることに感謝です。

[DBデザイン#47] VARCHARとTEXTどちらを使う?

ちょっと間が空いてしまいました。タイトルのテーマでサクッと書こうと思って調べ始めると、迷路に入ってしまいました。真面目に、全ての情報を書くのは多分、この話題はかなり辛いでしょう。特にMySQLの問題がありますから。ですので、いい感じのストーリにするのにちょっと時間がかかりました。マニュアルやブログを大量に読みましたが、なるべくシンプルにまとめたいと思っています。

文字列をフィールドに保存するとき、VARCHARかTEXTのいずれかの型を使うことになるでしょう。VARCHARはCHARACTER VARYINGの短縮形なのですが、VARCHARの方がお馴染みなので、こちらの表記にします。標準SQLではTEXTは定義されていないのですが、MySQL、PostgreSQLではこの2つの型のどちらかを使います。CHARという型もあるのですが、文字数を指定して定義し、足りない場合にわざわざ空白で埋めるようなことをするので結果的に固定長の文字列になりますが、現状のアプリケーションではそんなことは不要なことが多く、可変長の文字列型しかほぼ使わないでしょう。

そこで、2種類の型があるのですが、大きな違いはVARCHARは、VARCHAR(10)として、10文字(バイトではない)までの文字列を保存できるようなフィールドを用意するのに対して、TEXTは長さは指定せず、データベースの動作上の制限の長さまで入力できます。ここでいきなり例外が出ますが、PostgreSQLについては、VARCHARだけを書いて( )なしにすることで、長さの制限を無しにすることができるのですが、今回の記事では、VARCHAR(n)かTEXTかという表現で通します。

まず、2種類の型の大きな違いは、文字数の制限ができるかどうかです。文字数を制限したいかどうか? 実は制限しなくてもいいような場面が多いと思われます。逆に制限してしまった方が、意図せず制限より長い文字列を保存できなくなってしまうという問題に遭遇します。しかしながら、都道府県を保存するのにTEXTを使うのかという、かなりどうでもいいような問題に頭を悩ませてしまいます。つまり、せっかく制限が可能なのに利用しないのかというジレンマにつきまとわれてしまいますが、論理的に考えれば制限が必要になることはあまりないと思われます。

ただ、処理速度が違うだろうということは気になります。先に結論を言えば、ほぼ同じだと言えるくらいでしょう。PostgreSQLはマニュアルに、この2つの型は目立った違いがないと書いてあります。MySQLについては、1レコードのブロック内に、実際のデータを並べるのが基本動作ですが、TEXTについては原則としてそのブロック外にデータを保存するので、要するにVARCHARよりもTEXTの方が処理に時間がかかる傾向があるはずです。しかしながら、実際の運用等でもそんなに大きな差があるようには思えませんし、ベンチマークを公開しているブログ記事などを見る限りは僅かな差です。長大なデータ処理をするような場合には違いが出るかもしれませんが、一般的なUIを絡むようなアプリケーションでは多分違いはほぼ顕在化しません。ネットワーク等の他の要因の方がよほど大きく効いてくると考えられます。パフォーマンスに違いがないのなら、制約の少ないTEXTの方がいいじゃないかと思うところです。

しかしながら、MySQLに微妙な違いがあって、VARCHARはDEFAULTによる既定値の指定が可能なのに対して、TEXTはそれができません。PostgreSQLはTEXTでもDEFAULTの指定は可能です。そして、なんと、MySQL互換のMariaDBも、TEXT型でのDEFAULT指定はできます。互換を謳いながらもより高機能になっています。

この既定値については確かにデータベース定義上で行っておけば、プログラムコードは一切なく、フィールドの初期値が決められて便利です。しかしながら、都度、初期値が違うようなことも多く、結果的にプログラムで初期値を決める必要がある場合もそこそこあると思われます。長さの制限ができるという意味では、確かに不要な長いデータを保存してしまう危険性は排除できますが、本当に長さの制限をしたい場合は、おそらくUIで確認をして、長さが超えていればそもそもデータベースに命令を送ることはしないようなシステムの動作をすることができるわけです。ということで、初期値も、長さ制限も、システム内部でのケアがどっちにしても必要だとすれば、2つの型をチョイスする積極的な理由にはなりにくいでしょう。

ちなみに、SQLiteではTEXTのみです。FileMakerでも「文字型」としてフィールド定義できますが、長さの指定はできません。そんなわけで、PostgreSQLだとTEXTでいいだろうということになりそうです。MySQLではDEFAULTを設定したいかどうかによって変わるけど、長さ制限する意図がない場合にはTEXTで良いという結論になりそうです。

ただし、TEXTにも制限があるということを理解しておく必要はあります。PostgreSQLは1GBまでなので、ある意味、滅多にそこまでは行かないでしょうけど、MySQLは64KBの制限があります。しかしながら、これはバイト数なので、4バイト表現のUTF-8が基本となっているMySQLだと、最大で15,000文字くらいになり、書籍1冊分のテキストは入らないかもしれないぞというサイズです。MySQLは最大長がもっと多い、MEDIUMTEXTやLONGTEXTという型があるので、場合によってはそちらを使うことを検討する必要があります。

MySQLについては、文字列の全てがインデックスに利用されるわけではなく、Ver.5.7以降では3072バイトまでになります。また、インデックスを作成するときに、最初から何文字まで取り出すのかということを、VARCHARでもTEXTでも指定は可能です。さらに、LIKE演算子での検索では、LIKE ‘A%’のような前方一致の検索の場合しかインデックスは使用しません。長い文字列を扱う場合はインデックス対象になる範囲があることや、LIKE ‘%A%’のような〜を含むの検索をしたときにインデックスが使われないということにも注意が必要です。長い文字列に対しての検索の効率を上げるには、FULLTEXTインデックスという機能を使う必要があります。クエリーのSQLも若干違うものを使う必要がありますが、データが大きくて検索が遅い場合には検討する必要が出てくるでしょう。

MySQLのことを調べていたら、懐かしい「8KBの壁」の話が出てきました。MySQLは、8KBの領域を使って1レコードを管理する方法が取られていますが、Ver.5.6までの標準のセットアップでは、フィールドを増やせば、「思ったよりも早く」この8KBを埋め尽くしていたのでした。その時は、1つTEXTのフィールドは8KBのブロックのうち768バイトを使っていたので、10個ほどフィールドを作ればアウトだったのです。Ver.5.7からはTEXTはブロック内の20バイト程度しか使わないので、まずまずたくさんのフィールドが定義できたのでした。MySQLにはこうした「意外な頭打ち」がいろんなところにあるので、使いこなしたい方は、まずはマニュアルを熟読することが必要になるでしょう。

[DBデザイン#46] フィールドはどこまで細かくすればいいか

以前にフィールドに入れるデータは、元々どんなデータが入るのかという母集団的な集合があって、そこからチョイスされたものが入力されるという「ドメイン」の概念を説明しました。母集団は「定義域」とも言われますが、記述可能、不可能があるとしても、ともかく概念としては、ドメインがある前提です。それぞれの要素を記述するために、「ラベル」としての文字列が使われることが多いが、本質は文字列を記憶しているのではなく母集団の中の1つの要素が記述されているということです。

ここで、名前を記録したいと思った時、まずは姓と名をどうしようということが思い浮かぶと思いますが、分離分割するとしても、「名前の1文字目」「名前の2文字目」…などとさらに分割するようなことは考えません。私の姓である「新居」は、おそらく、日本人の姓の定義域に定義があって、「新」と「居」に分離してしまうと、姓の定義域から遠い、単なる漢字1文字になってしまいます。データベースのテーブルでのフィールドは、原則として何かの意味を持ちます。定義域があるという定義がその根拠になります。

では、姓と名はどうでしょう。データベースの設計として、ここは重要なところです。一般には、姓と名を分ける方が合理的とは言えます。なぜなら、「別々に扱う」ことがよくあるからです。例えば、名前が入った一覧表を作るときに、必ず姓名を表示する場合もありますが、姓だけで構わないというような場合があるとするとします。もし姓名をまとめて1つのフィールドに入れていた場合、そこから姓を分離するのは少し厄介です。日本人は大体姓も名も漢字では2文字だからというと、姓が1文字、名前は3文字という人もいらっしゃるわけで、確実に姓だけを取り出すことはできません。そこで、考えるのは、「姓と名の間は必ず全角のスペース」として、入力時にきちんと検証するという方法です。もちろん、それでうまく行きますが、データ構造からUI設計まで巻き込んで、姓名の分離が可能なデータ入力とその保持を行うというのは、なんだかコストに影響しそうな気がします。最初から分離しておけば、その問題はありません。非常に大雑把に言えば、データベースのフィールドは分割するのは大変というか、データベースの設計を超えた仕様の検討が必要になるのが一般的です。一方、データベースのフィールドを結合するのは、ビューを利用することで簡単に可能です。Webページだと、単にフィールドのデータを並べて配置すれば済みます。分割は大変だけど、結合は簡単ということから、分割可能なものは分割しておくのが良いということになります。

ただ、姓名分割方式だと、ミドルネームの扱いをどうするのか、更には、Family NameとGiven Nameの区別を意図通りしてもらえるのかどうかなど、外国の人の名前を巻き込むともっといろいろ考えないといけないかも知れません。今時はUnicodeでシステムは動くので、「入らない文字がある」という不便さはほぼなくなりつつはありますが、フィールドをどう用意するかは悩ましいところです。「名前」という1フィールドにして、とにかくその方を特定できる名前であればなんでもいいから入れておいてください的な割り切りがあるのなら、姓名の分割は不要です。その場合、姓だけを抜き出すというようなことは要求にないなど、名前フィールドの中身を系統的に分割して利用するということはしないという前提があります。例えば、送り状の宛先に使うだけだとかいった場合は、わざわざ姓名で分離しておく必要はおそらくはないだろうということです。もちろん、ここで、要求がしっかり確定していないといけません。このような場合で、データがすでにたくさん入ってしまった後から、やっぱり姓と名は分けましょうというのは、もはや簡単に移行できないことになってしまっているわけです。

名前と同様、住所も悩ましいですね。郵便番号+都道府県名+市区町村名+町域名+番地+ビル名と室名、といった分離をするというアイデアから、逆に全部ひとまとめにして住所フィールドというやり方まで、どうすればいいでしょうか。基本的には名前と同じです。住所を単に封筒などへ印刷するときの宛先としてしか使っていないのなら、まとめてしまってもいいでしょう。分割してしまうと、必須のフィールドとオプションのフィールドがどうしてもできてしまうのですが、番地を必須にすると、北海道の大自然に囲まれた場所だと番地がなかったりして、エラーで先に進みませんとクレームになるかも知れません。ですが、1つの住所フィールドなら、郵便物や宅急便が届くようにとにかく好きに入力して貰えばいいのです。届かないのは入力した人が悪いと言えます。

しかしながら、分割するメリットはないのでしょうか? それは大いにあって、住所を分割しておくことで、分類が非常にスムーズになり、集計や分析がしやすくなります。例えば、住所以外にアンケート結果なども一緒にあるとしたら、アンケートの回答について、都道府県別に集計するようなことが非常に容易になります。もっとも、そうした分類に使うとしたら、市区町村名までで、残りは一括でもいいような気がしますが、いずれにしても、後から都道府県名だけを取り出すということに力を注ぐよりも最初から分割されている方がスムーズなのはもちろんです。住所を1フィールドで運用してどうぞご自由にとしたら、きっと都道府県名は書いたり、省略したりということで、必ずしも含んでいるとは限らないでしょう。

結果的に、フィールドをその後に何に使うのかということを仕様として織り込んでいるのかで、その細かさあるいは荒さが決まるということです。さらにそれを突き詰めると、どんな定義域を想定しているのかということに他なりません。

ところで、一般には住所は「郵便番号+都道府県名+市区町村名+町域名+番地+ビル名と室名」と分離して、それぞれ入力したりします。ただ、それだと不便だということで、郵便番号を入力すると、ルックアップして自動入力なんてUIもよく見ます。郵便番号は全ての住所を網羅しているので、{郵便番号}→{都道府県名, 市区町村名, 町域名}という関数従属性はありそうです。であれば、まさに表を分離して、郵政事業が配布している郵便番号のテキストファイルの内容を保持する表を用意し、住所の記録が必要な表では住所は、{郵便番号, 番地, ビル名と室名}だけでいいのではないかとも言えるかも知れません。もしくは、さらに割り切って{郵便番号, 残りの住所}でもいいかも知れません。郵便番号で照合すると元の住所は再現できるので、理屈ではそうなるでしょう。それでも、都道府県名をフィールドとして用意して入力させるのはどうしてなのでしょう?

もはや住所は文字としては入力していないのかも知れないと思わせるのが、ヤマト運輸のクロネコメンバーズのアドレス帳です。本当にどういうデータベース設計しているのかはわかりませんが、常に郵便番号から自動的に選択させ、番地等も選択入力をさせられます。おそらく、町域名よりも細かい住所データがあって、それを突き合わせながら入力することで、存在しない入力を極力避けることを意図しているのでしょう。それは、配送の効率化という意味では納得のいく対策です。

しかしながら、多くの方は、今でも、念の為に、郵便番号以外に都道府県名から文字列で入力しているでしょう。なぜか? 1つは仮に郵便番号から住所の一部を取ってくるという運用をするには、郵便番号と住所のメンテナンスが必要です。全国のデータが掲載しているファイルは1ヶ月に1回更新されており、最近は少ないですが自治体の合併や、住所の変更などが随時反映されていないければなりません。また、なくなる町名の扱いをどうするなども対処が必要です。要するにで、メンテナンスをきっちりとやらないといけないという点で、まずはコストとのバランスで多くの場合は躊躇してしまいます。

この点を除けば、都道府県などはフィールドとしてははいらないとも言えそうですが、今でも、郵便の表に書く宛先には、郵便番号だけでなく、都道府県はまだ一部省略するとしても、市区町村や町域名は書くわけです。もちろん、そこは差し出す人、受け取る人が見るということで、人間向けのUIであるという見方もできますが、住所は少々冗長であっても省略しないで記述することに慣れ切っている私たちにとっては、いきなりデータベースの理論上これは不要だと言われてもちょっとねーと思ってしまう側面があるのではないかと推測されます。少なくとも、入力時に都道府県を入れない入力ページはインターネットのベテランでも首を傾げそうです。習慣を変えるのは、データベースの設計を変えるよりも大変なのです。

[DBデザイン#45] ルックアップを設計に巻き込む(更に続き)

ルックアップの話は3日かかりました。今回で話は終わるはずです。ここまでに、ルックアップの機能を説明し、便利ながらも正規形の議論から外れた設計になっているとも言えるという話をしました。以下はルックアップによって、「商品名」「単価」が別の商品の表から複製された販売明細です。

一方、以下は、上記の表で、{商品ID}→{商品名}、{商品ID,(販売日)}→{単価}という関数従属性があることを見つけたとして、テーブルの分解をおこなった場合です。販売日と、開始日及び終了日で表現される「販売期間」の照合は、大なりや小なりを使って検索条件を組み立てないといけないので、商品IDのような単純な=での照合よりもかなりややこしくなっていますが、理屈としては理解していただけると思います。

このような場合、後者のテーブル分割をした結果の方が、データベースの理論上は「正しい」とも言えるのですが、実はいくつかの点で躊躇してしまいます。どちらの設計で進めるかはかなり考えないといけませんし、発注側と協議も必要です。何を懸念しているかと言えば、まずは、「新しい要求を作ってしまっていないか」という点です。単に販売の記録をしたいだけだったのに、単価の管理という新しい機能が増えています。もちろん、開発の作業量が増えることもありますが、それよりも、利用者のワークフローに変化が出てきて、場合によっては作業量は大きく増えます。要するに、「かえって面倒になる」機能を実装しようとしていないかという懸念があるのです。利用者の要望が「ともかく伝票が作れたらいい、価格はその都度変えて、たとえば、3年前の5月はいくらだったかというのは伝票を見れば良い」というくらいのことであれば、テーブル分解をしていない手法の方が何かとお手軽であることは言うまでもありません。一方、利用者が、「それでは単価テーブルをきちんとメンテナンスしましょう」という気持ちになっているのなら、テーブル分割することによる意義はあるでしょう。もちろん、今回のサンプルであれば、「過去の単価の変遷をきちんと追える」といったメリットは言えるのですが、それがどうしたと言われればメリットにもなりません。色々な立場で、手間との天秤をかけることになります。検討するためには、テーブル分割した時のメリットやデメリットをしっかりと把握しましょう。

仮にテーブルを分割したとしても、前回説明した「前提に合った」正しいデータであるという状態を保持するために、色々な対策を考えないといけません。少なくとも、ユーザーインタフェースにそうした仕組みを入れないといけないでしょう。まず、同一商品で期間が重複していないようにしなければなりません。もし、重複した期間があった場合、その期間内の日に販売日が入っているとしたら、その結果は合成した表では2つのレコードとして登場します。1件の販売が2件の販売に膨れ上がってしまうので、仮に単価が同じであっても事実を曲げていることになり、これは問題です。入力時にチェックすればいいというとそれまでですが、実はこの期間の重複がないという判定は、レコードをまたがって判断が必要になるため、単純な式では求められません。どう実装するかは工夫のしどころでしょう。画面上に同一商品で検索した結果を一覧して、下から舐めて、重複がないかをチェックするなどの方法が考えられますが、いずれにしても、そうしたチェックなしにかかると期間の重複が発生する可能性が高いため、必ず設計に盛り込まないといけません。また、終了日に遠い日を設定するというやり方で説明しましたが、前回にも指摘したように、NULLを入れることで未来永劫を示す方法にしたい場合には、それも対処が必要になります。

では、ルックアップで済ませることでも概ね大丈夫な線引きはどの辺にあるのかと言うことですが、これは一概に言えないものの「紙で運用していたもの」は、感覚的な話になりますが、だいたいルックアップの方が実情に合っていると言えるかもしれません。たとえば、病院のカルテなどは、昔は紙の束だったので、要するに、リンクしていると言う概念はほとんど及びません。その時に記入したら、それが残るのです。ある患者の保険証番号は1つに決まるはずですが、転職などして変わることもあります。その変化を緻密に追うことは病院業務としては考えにくく、診察に来た日の保険証番号が、その日のカルテに記載され、請求書や領収書に使われるとして、要するに、「今現在の保険証番号」が患者マスターにあって、診察日にはそれだけが関心の対象であるので、それを「複製」して使用するレイアウトで見えていればいいわけです。むしろ、保険証番号を参照して表示しているとしたら、番号が変わった場合に過去の紙帳票の内容も変わってしまって、これはむしろまずいでしょう。もちろん、診療録関連でも期間を管理する必要があるようなデータもあるかも知れませんが、概ね紙で運用していた資料にあるようなものは、ルックアップでことが足りる事が多いと言えます。

一方、ルックアップで実装すると、単価や商品名などを「編集可能」にすることもよくあります。間違えた時などに手軽に処理をしたいというような事があります。ですが、商品IDを切り替えずに、手で商品名と単価を入れるのは、参照不整合を起こさせるようなものとなります。編集は気軽にできないようにするのが1つの方策ですが、利便性のために利用者からは編集可能にしてほしいと言われるかも知れません。そうなると、きっと、商品名のところに、前の行の商品の説明として「納期は今現在未定です。」みたいな注釈を書いてしまうかも知れません。それでも、商品IDが未選択なら問題はないとも言えます。多くの人は、表形式の画面を見るとExcelを起想します。下手をしたら、「なんでここで下にコピーできないんだ!」とまで言われることもあります。それは極端な例としても(でも、Webアプリで言われたことありますね)、Excelの時のように入力し、編集したいとどうしても思ってしまいます。この辺りは、運用方法を開発者が想定する適切なものでとお願いしても変えてくれないこともよくあります。つまりは習慣化してしまっているのでしょう。本来、注釈があるなら、別のフィールドを用意するのが正しい対策ですが、編集可能にした結果、意図せずそういう使われ方をすることは十分に考えられます。いずれにしても、フィールドを編集可能にしてしまうのかどうか、よく検討が必要です。編集できないのは不便です。できると統制が取れなくなってしまう可能性があります。これは、落とし所が難しい問題になります。

ここでの単価の表のような事例としては、人事の記録があります。ある期間にどの部署に所属していて、どんな肩書きだったのかと言うような記録が確実に取れます。肩書き等が他のデータに対して影響のあるようなシステムの場合は、やはり、期間を記録する表は必要になります。ある案件で、そう言うものを作った事があるのですが、元々、人事のためのマスター的なものがなかったのか、あるいはシステム適用の範囲内で存在しなかったのか、ともかく、その表のメンテナンスという新しい仕事が発生してしまい、システムが使われないままになった原因の1つを作ってしまったという残念な経験があります。

システムの設計にはトレードオフは至ることで出くわします。データベースの設計だけなら、テーブル分割することでの効率化や、データの多角的な利用といういいことばかりが見えるのですが、システム全体として、そしてそれを利用する人たちのワークフローにどんな影響を与えるのかということを常に俯瞰しながら設計を進め、実装を行い、検証を行うという必要があります。まずは、設計の上でトレードオフが発生し、それぞれのメリットやデメリットを把握することが重要です。その上で、発注側あるいは利用者に丁寧に説明をして、判断ができる状態を作って共同で検討を進めるのが理想的でしょう。