回顾一个多月php与Yii与Oracle的碰撞

临时抽调隔壁组参与一个半独立项目(其实还是在同一个办公室), 主要负责后台内容的维护, 权限管理部分为JAVA的上司, 数据库为oracle.
　　刚开始时当然会因为换到一个不熟悉的数据库而担心出现很多解决不了的问题, 还好框架解决了最基本的事情：同样的方法仍能完成普通的增删改查, 不然要框架做什么呢不是? 然而在开发中还是遇到了一些问题, 于是特地回顾记录一下.

oci扩展

首要的问题就是安装php的oci扩展和oracle数据库, 不过数据库在内网已经搭建好了直接连接即可, 所以这部分就没有接触到了.. 至于oci扩展由于时隔久远(1个月多了), 只记得一定要选择和oracle数据库版本和位数一致的。(这里是oralce 11gR2 64位). 最后的db配置：

'db' => [
    'class' => 'yii\db\Connection',
    'dsn' => 'oci:dbname=//#ip:#port/orcl;',
    'username' => '#username',
    'password' => '#password'
]

自增

oracle中是没有自增的...在开始没有被告知的情况下(上司说是忘了说), 开始自己寻找解决办法：

• 序列(+触发器)：
  　　序列(sequence)的概念是在oracle中接触到的：用于生成连续的整数数据的对象, 常用来作为主键的增长. 也就是说, 只要在新增一条记录前, 使用序列获取最新的序列值并赋予主键即可, 而这配合触发器使用就比较方便.
  　　但是序列的问题就在于, 每一个序列应对应一张表的主键, 而当时的情况是已经创建好30多张表了, 不应再去为每个表的主键创建相应的序列了(因为这是在创建表之前就应该考虑好的问题); 而所有表共用一个序列也是不应该的, 共用会导致序列的值(1,2,3....N)分散在各张表, 而不是每张表都是1至n.
  　　另外非不得已的情况是不使用触发器的好, 以减少数据库的消耗
  　　于是自认为应舍弃此种方法.
• sys_guid：
  　　使用系统函数sys_guid生成不重复的字符串(UUID)赋予主键
  　　SELECT sys_guid() FROM dual
  　　但可以发现返回的是乱码, 需要使用rawtohex函数转换一下, 即
  　　SELECT rawtohex(sys_guid()) FROM dual
  　　由于字符串不会重复, 似乎可行, 加上想起从认识的java同学听说他们的主键都是字符串而不是常规的数字就更加说服了自己这是最好的解决办法.
  　　然而此时才发现主键全都是NUMBER类型...于是只能再次舍弃这个似乎可行的方法.
  

  ---

  
  　　询问过上司后才知是忘了被告知这个自增的事情, 直接使用由他自定义的一个函数来得到主键的值，而这个函数其实还是通过序列的方式返回值, 使用SELECT * FROM user_sequences后才发现其实序列早就已经建立好了.. 也就是说还是使用了序列的方式.
  　　于是开始实现：在BaseModel中加入自定义行为, 并绑定beforeInsert事件(行为的名字就实在不会取了)：

// BaseModel
public function behavoirs()
{
    return [
        ...
        'id' => [
            'class' => IdBehavior::class
        ]
    ];
}

// IdBehavior
public function events()
{
    return [
        BaseModel::EVENT_BEFORE_INSERT => 'setId'
    ];
}

public function setId($event)
{
    $model = $event->sender;
    $tableName = $model::tableName();
    $sql = "SELECT function('{$tablenName}') FROM dual"; // function代表一个自定义获取序列值的函数
    $model->id = Yii::$app->db->createCommand($sql)->queryScalar();
}

clob字段类型

• 存储
  　　clob可认为是长字符串的存储类型(实际是LOB型的大型对象), 对于一定长度的字符串, 并不需要做任何处理即可存入. 而当字节数大于4000时 如果还是一般的处理, 则会得到这样的报错
  ORA-01461: can bind a LONG value only for insert into a LONG column
  也就是字节超长了（考虑到PDO默认STR的长度值）
  此时应舍弃AR的便利操作, 转为近乎原始PDO的写法(update举例,insert相同)

$file = './example.png';
$content = file_get_contents($file);
$length = filesize($file);
$sql = 'UPDATE table_name' .
        'SET clob_column = :clob' . 
        'WHERE id = :id';
Yii::$app->db
        ->createCommand($sql)
        ->bindParam(':clob', $content, \PDO::PARAM_STR, $length)
        ->bindParams(':id', $id)
        ->execute();

此处使用file_get_contents获得的值模拟长字符串, 其中在bindParam中指定$length是最重要的一点(尽管超过4000字节, 也仍能正常存储).
　　另外虽然是clob类型字段, 若在此处指定类型为\PDO::PARAM_LOB, 则又会得到这样的报错:
ORA-00932: inconsistent datatypes: expected CLOB got BLOB
根据报错可猜测\PDO::PARAM_LOB是针对于blob类型的, 而此处的clob类型还是作为字符串处理

• 取出
  　　只要clob类型字段取出的值不是null, 那么它一定是一个resource, 此时只需stream_get_contents一下即可得到原来的字符串.
  　　另外如果使用了GridView来做列表展示, 并给出了clob类型的字段, 在多条信息时, 可能会发现所有的该字段显示的是同一个内容. 猜测可能是GridView中的处理问题(凡是遍历模型并通过stream_get_contents都有这样的问题), 于是只好再在每个模型中根据自身id查出一次clob字段的值并stream_get_contents一下(参考isssue#3167

blob字段类型

上司这边的要求是图片、视频、音频资源全部存储到数据库的blob字段(无法驳回这种做法, 无奈), blob是存储二进制数据, 此前并没有存储过, 然后经历1天多的折腾最后才在官方PDO的手册中找到解决方法(example 3), 关键点在于开启事务否则只会隐式提交0长度. 以下是各种写法的尝试以及报错 （以图片存储为例）

• 普通存储

$file = './example.png';
$content = file_get_contents($file);
$sql = 'UPDATE table_name' .
        'SET blob_column = :blob' . 
        'WHERE id = :id';
Yii::$app->db
        ->createCommand($sql)
        ->bindParam(':blob', $content)
        ->bindParams(':id', $id)
        ->execute();

报错： ORA-01461: can bind a LONG value only for insert into a LONG column
指定字符串类型与长度后bindParam(':blob', $content, \PDO::PARAM_STR, filesize($file))报错同上

• 指定类型为LOB

$file = './example.png';
$content = file_get_contents($file);
$sql = 'UPDATE table_name' .
        'SET blob_column = :blob' . 
        'WHERE id = :id';
Yii::$app->db
        ->createCommand($sql)
        ->bindParam(':blob', $content, \PDO::PARAM_LOB)
        ->bindParams(':id', $id)
        ->execute();

报错supplied argument is not a valid stream resource 提示非resource类型于是舍弃file_get_contents方法

• 使用fopen(r模式

$file = './example.png';
$content = fopen($file, 'r');
$sql = 'UPDATE table_name' .
        'SET blob_column = :blob' . 
        'WHERE id = :id';
Yii::$app->db
        ->createCommand($sql)
        ->bindParam(':blob', $content, \PDO::PARAM_LOB)
        ->bindParams(':id', $id)
        ->execute();
fclose($content);

正常运行但数据库保存为空

• 更换fopen模式为rb, 并使用empty_blob()函数和RETURNING INTO

$file = './example.png';
$content = fopen($file, 'rb');
$sql = 'UPDATE table_name' .
        'SET blob_column = empty_blob()' . 
        'WHERE id = :id' . 
        'RETURNING blob_column INTO :blob';
Yii::$app->db
        ->createCommand($sql)
        ->bindParam(':blob', $content, \PDO::PARAM_LOB)
        ->bindParams(':id', $id)
        ->execute();
fclose($content);

fopen的rb模式似乎不怎么常用, 它使用二进制模式读取文件, 而此处正是需要使用这种模式
　　使用oracle的empty_blob函数初始化blob字段, 而此处的RETURNING INTO 更像是能为这个初始化做一个占位符的指定, 否则使用函数的同时无法再指定占位符.
　　似乎根据报错已经来到了最终的解决方案, 然而数据库保存仍未空.

• 开启事务
  　　都说google好, 那真不是假的, 同时百度、googlepdo blob关键词的结果, 只有google成功引导至官方文档.. 之前的时间都花在寻找也许有人遇到同样的问题会在博客中或提问中提到, 然而结果就是瞎转悠浪费时间.
  　　而上述仅差一个事务解决, 于是最终的写法便是

$file = './example.png';
$content = fopen($file, 'rb');
$sql = 'UPDATE table_name' .
        'SET blob_column = empty_blob()' . 
        'WHERE id = :id' . 
        'RETURNING blob_column INTO :blob';
$transaction = Yii::$app->db->beginTransaction();
try {
    Yii::$app->db
            ->createCommand($sql)
            ->bindParam(':blob', $content, \PDO::PARAM_LOB)
            ->bindParams(':id', $id)
            ->execute();
    $transaction->commit();
} catch (Exception $e) {
    // todo
}
fclose($content);

---

　　对于取出后的注意点和clob都相同. 只是stream_get_contents后不能直接使用(二进制流), 这里的做法是base64_encode处理后再使用img、audio、video标签展示出(文件格式、以及标签使用base64这里不做说明, 这边对于音频、视频的不同格式的处理为统一使用ffmpeg转换mp3、mp4再存储）

最后

由于此项目已暂停(上面没有谈好钱的问题, 但并没有取消), 暂时遇到的问题只有这一些, 之后如果再出现新的问题会及时记录.

转载于:https://www.jianshu.com/p/4dcf4b1c527e