Giving up on Yii Oracle Clobs

Started off yesterday with the intention of trying to implement functional Oracle Clobs in Yii similar to how I implemented it in CakePHP a couple years ago. As yesterday went on I kept busting through boundaries and was feeling great about my progress. But Then I hit the roadblock and I wasn’t able to continue. It’s not worth spending another couple days on when I have a deadline for a pilot by Fall. 4000 characters is enough for the pilot.

The result has been to alter the Yii COciSchema. Just changing the declaration of text to a varchar2(4000).

   public $columnTypes=array(
        'pk' => 'NUMBER(10) NOT NULL PRIMARY KEY',
        'string' => 'VARCHAR2(255)',
        //'text' => 'CLOB',
        'text' => 'VARCHAR2(4000)',
        'integer' => 'NUMBER(10)',
        'float' => 'NUMBER',
        'decimal' => 'NUMBER',
        'datetime' => 'TIMESTAMP',
        'timestamp' => 'TIMESTAMP',
        'time' => 'TIMESTAMP',
        'date' => 'DATE',
        'binary' => 'BLOB',
        'boolean' => 'NUMBER(1)',
		'money' => 'NUMBER(19,4)',

The issue is Yii (PDO) doesn’t support LOBs well at all, so that declaration didn’t make any sense to begin with.

Yii handling “getLastInsertId” with Oracle

With MySQL or SQLite, when you insert something with an auto_increment field, it will automatically deal with the ActiveRecord by putting the last inserted id into $model->id. Because Oracle needs sequences and triggers to deal with that, neither the PDO driver nor the Yii PDO code felt it necessary to deal with that. Most people are probably fine with commiting to a database and throwing in Oracle specific sequence_name.nextval.

I just added a class in the models to extend CActiveRecord. The only thing you need to do with this is have your model extend this instead of CActiveRecord and add the variable schemaName to the model:

class User extends QActiveRecord
	public $sequenceName = 'USERS_SEQ';