Yii and Oracle DB

hello people, i’m planning to use Yii with oracle db, so please tell me your thoughts about “oracle + yii”?

Sure! Simply configure oci driver and php in your system and then use string connection in yii.

Then in your config:




[

	'class' => 'yii\db\Connection',

    'dsn' => 'oci:dbname=//localhost:1521/mydatabase', // Oracle

    'username' => 'root',

    'password' => '$123',

    'charset' => 'utf8',

	];



I’m making a project oracle based for local development I use mysql with an equivalent schema as the oracle one and I can switch database without problem.

[b]

Mysql vs Oracle[/b]

Compared to mysql I have much worse performance, in many cases I had to use eager loading for the query.

Unfortunately I do not manage the oracle database and the DMZ configuration so I can’t verify if this is a problem of bad Oracle indexing, problem with the network or something else.

Yii2

On yii2 side I can say that it works great with both, just as side note GII does not see the oracle view (didn’t test mysql view as I do not use them) to auto suggest the table name, but if you type it by hand it works as expected.

On oracle you can use mixed case table and filed name as yii2 handle them without problem (I think oracle naming convention all upper-case is horrible and difficult to read)

Coding

The only notable issue I have is the absence counter column in oracle as it use sequence.

I resolved this by overriding the beforeSave() of the model.

I check if the driver is oracle and if I’m doing an insert .

Then from the table name I obtain the sequence name and the column id name and I set it with sequence.nextval

In this way you don’t have to get the sequnce.nextval in the actionInsert of every controler.

If you think you could need the code snippet I’ll post it

The other way (which is the dba approach) is to put a trigger on the ID column on every table on oracle side but this has the drawback that you don’t know the generated id.

This value comes handy when you have a form where you fill in a record and some other related data, you need the id to insert related data.

Hope it is useful.

@Roberto Braga

I can implement your idea of Oracle sequence, but my way I need to implement it, every model that I have.

Do you implement it the same way?

I think that it must be implemented in other model or implement some pattern to reuse the code.

I made a class that extend Active Record and all my model extend this class

This class override the beforeSave of Active Record and has some code that calculate the sequence name.

They Way I calculate the sequence name probably does not fit to your naming convention, but it can give you some hint on how to do for your case.

Here is my code:


class MyActiveRecord extends \yii\db\ActiveRecord {


    public function beforeSave($insert) {

        // The following if is to execute the "sequence guessing"

    	// only for oracle and if it is new record

        // probably you don't need to check for oci (I was using the same schema also on mysql)

        // and you can omit it

        if ($this->getDb()->getDriverName() == 'oci' && $this->isNewRecord) {

            // my sequence name is based on the ID filed name which is based on table name

            // So for the table SP_CAT_CATEGORIA

            // the id filed is CAT_ID_CATEGORIA and

            // the sequence is S_CAT_ID_CATEGORIA

            $arr = explode('_', $this->tableName());

            unset($arr[0]);

            $arr[1] = $arr[1] . '_ID';

            $idName = implode('_', $arr);

            // sequence name

            $sequence = substr('S_' . $idName, 0, 30);

            $query = new \yii\db\Query;

            $query->select($sequence . '.NEXTVAL')->from('DUAL');

            $rows = $query->all();

            $this->$idName = $rows[0]['NEXTVAL'];

        }

        // it is necessary to return true otherwise it won't save

        return true;

    }

}


// all my model extend the above class


class SPCATCATEGORIA extends MyActiveRecord

{


}

Hope it help.

Thanks @Roberto, it’s help me a lot.