CakePHP + Oracleにおける4,000バイト制限をどうにかする

以前も触れましたが、CakePHP + OracleでWEBシステムを構築する案件を進めています。

そもそもCakePHPはデフォルトでOracle用のドライバーが用意されておらず、有志が作成したものを使うしか無い状況に加え、date形式のカラムへの日時のインサートがそのドライバーでは対応出来ないという、厳しい状態です。

結局、単純な呼び出しはCakeを使う事が出来るものの(それでも日付は時間が取り出せない)、インサートやアップデートはモデルを使わずにsqlを走らせるしか無いようです。

例えば以下のような感じ

App::import('Model', 'ConnectionManager');

$db = ConnectionManager::getDataSource('default');
$sql = "UPDATE xxxxxx SET XXXX";
$result = $db->query($sql);

Cakeの縛りから開放されますので、複雑なjoinなども割と容易く実行出来ます。

さて、Oracleには発行するSqlに4,000バイトの制限が掛かっており、これを超えるとエラーとなります。
ORA-01704: 文字列リテラルが長すぎます

これがとても大変というか、そもそも日本語は2~4バイトあるので単純に２バイトとしても2,000文字、しかもｓｑｌ文もこれに含まれるので、送信する文字列は更に少なくなります。

こういう場合はlob系を指定し、別な場所に格納する、という事らしい
http://www.shift-the-oracle.com/table/lob-storage.html

そもそも日本語はUnicode+PHPだと４バイトになる文字も有り、そうなるとフォームから送信出来るデータは、500文字程度にしないと安心出来ない。500文字って、ちょっとしたお問合せ内容なんかだと簡単に記入する量なので、そもそも無理があるのだと思います。

Googleなど大手でもMySQL(現在はmysql派生MariaDB)をチューニングして採用しているなど、WEBサービスではMysqlやPostgresの方が取り回しが楽なのです。

ですが、クライアントからcake+Oracleを指定されている以上はどうしようも有りません。

今回のテーブルの構造と制約

テーブルを変更する際に、それを上長が許可する事で実行される、いわゆるワークフローが必要で、新旧データを格納するテーブルが必要になります。更新がいつ行われ、どのデータをどう変更したか、誰が承認したかなどを記録する為のテーブルです。

また、これには個人情報が含まれる可能性が有り、暗号化も必要になります。但し、用意してくれたOracle暗号化classは単純な同一文字列での暗号化で、rijndael-256のようなものではなく(これはパッケージ会社の指定)、ちょっと怖い。ですので、cakeで暗号化して保存します。

Crypt.php

暗号化用の共用classを作成するため、Vendorあたりに適当なファイルを作ります。

class Crypt {
    static public function encrypt($text) {
        return Security::rijndael($text, Configure::read('Security.salt'), 'encrypt');
    }

    static public function decrypt($text) {
        return Security::rijndael($text, Configure::read('Security.salt'), 'decrypt');
    }
}

beforeSave

    public function beforeSave($options = array()) {
     //新データ 暗号化
      if (isset($this->data[$this->alias]['new'])) {
        $value  = gzdeflate( $this->data[$this->alias]['new'] , 9); //圧縮
        $value  = Crypt::encrypt($value); //暗号化
        $value  = base64_encode($value);  //base64
        
        $this->data[$this->alias]['new'] = $value;
      }
     //旧データ 暗号化
      if (isset($this->data[$this->alias]['old'])) {
        $value  = gzdeflate( $this->data[$this->alias]['old'] , 9); //圧縮
        $value  = Crypt::encrypt($value); //暗号化
        $value  = base64_encode($value);  //base64
        
        $this->data[$this->alias]['old'] = $value;
      } 
    }

該当モデルのbeforeSaveで、新データ(new) 旧データ(old) の処理を行います。

1) gzdeflateで文字列を圧縮する
2) Crypt::encryptで暗号化
3) base64_encode

AppModel

データを呼び出すとき、そのままでは意味不明な文字列ですので、AppModelへ復号化などを行う処理を書きます。

class AppModel extends Model {
    public function afterFind($results, $primary = false) {
        foreach ($results as $key => $val) {
            if (isset($val['MODEL']['new'])) {
              $value = base64_decode($val['MODEL']['new']);
              $value = Crypt::decrypt($value);
              $value = gzinflate($value);

              $results[$key]['MODEL']['new'] = $value;
            }
            if (isset($val['MODEL']['old'])) {
              $value = base64_decode($val['MODEL']['old']);
              $value = Crypt::decrypt($value);
              $value = gzinflate($value);

              $results[$key]['MODEL']['old'] = $value;
            }
        }
        return $results;
    }
}

暗号化のときの逆をやる、という事です。

当初はシリアライズするなど、色々手を考えてみたのですが、どちらにしても文字列が多くなれば4,000バイトに引っかかってしまうので、これまであまり使う機会の無かったgzinflateを使う事で圧縮しました。

gzinflateそのものは日本語でテストすると、圧縮比9でだいたい1/3程度に圧縮されるようです。

だいたい50カラム程度のテーブルの新旧履歴を格納しても大丈夫なので、テキストエリアをある程度の文字数に制限すれば、よほどの事が無い限り大丈夫そうです。

Oracleを使ったWEBシステム構築の経験が有る、とは言えない程度しか経験が無いのですが、改めて勉強をしつつ開発を進めると、どうにもこうにも扱いづらい。

オラクルマスターには最高のRDBで、これ以外は無いと思えるほど素晴らしいのでしょうが、WEBベースで考えると正直、使えない子としかまだ思えません。ましてそれをCakePHPという縛りの中でやるのは、指定が無い限りやめた方が良いと思われます・・・Symphonyやzendあたりでもおそらくこの解答になりそうな予感