neconix/yii2-oci8

Yii2 extension which uses well written pdo-via-oci8 package for standard PHP Oracle functions (OCI8)

Yii2-Oci8

Yii2 OCI8 extension which uses well written yajra/pdo-via-oci8 with optional full table schema caching. Supported PHP7., _(*1)

Supported - Yii 2.x; - yajra/pdo-via-oci8 1.x; - >= PHP 5.4; - >= PHP 7.0., _(*2)

Installation, _(*3)

Add to your composer.json file:, _(*4)

   "require": {
     "neconix/yii2-oci8": "1.*"
   }

And then run composer update., _(*5)

Yii2 configuration example for an Oracle database, _(*6)

Yii2 configuration:, _(*7)

$config = [
    ...
    'components' => [
        ...
        'db' => require(__DIR__ . '/db.php'),
        ...
    ]
];

Database configuration in db.php:, _(*8)

return [
    'class' => 'neconix\yii2oci8\Oci8Connection',
    'dsn' => 'oci:dbname=//192.168.0.1:1521/db.local;charset=AL32UTF8;',
    'username' => 'user',
    'password' => 'pass',
    'attributes' => [ PDO::ATTR_PERSISTENT => true ],
    'enableSchemaCache' => true, //Oracle dictionaries is too slow :(, enable caching
    'schemaCacheDuration' => 60 * 60, //1 hour
    'on afterOpen' => function($event) {

    /* A session configuration example */
        $q = <<<SQL
begin
  execute immediate 'alter session set NLS_SORT=BINARY_CI';
  execute immediate 'alter session set NLS_TERRITORY=AMERICA';
end;
SQL;
        $event->sender->createCommand($q)->execute();
    }
];

Example, _(*9)

Feel free to use Yii2 ActiveRecord methods:, _(*10)

$cars = Car::find()->where(['YEAR' => '1939'])->indexBy('ID')->all();

Getting a raw database handler and working with it:, _(*11)

$dbh = Yii::$app->db->getDbh();
$stmt = oci_parse($dbh, "select * from DEPARTMENTS where NAME = :name");
$name = 'NYPD';
oci_bind_by_name($stmt, ':name', $name);
oci_execute($stmt);
...
//fetching result

Caching features, _(*12)

To enable caching for all tables in a schema add lines below in a database connection configuration db.php:, _(*13)

    ...
    //Disabling Yii2 schema cache
    'enableSchemaCache' => false

    //Defining a cache schema component
    'cachedSchema' => [
        'class' => 'neconix\yii2oci8\CachedSchema',
        // Optional, default is the current connection schema.
        'cachingSchemas' => ['HR', 'SCOTT'],
        // Optional. This callback must return `true` for a table name if it need to be cached.
        'tableNameFilter' => function ($tableName, $schemaName) {
            //Cache everything but the EMP table from HR and SCOTT schemas
            return $tableName != 'EMP';
        }
    ],
    ...

Table schemas saves to the default Yii2 cache component. To build schema cache after a connection opens:, _(*14)

    'on afterOpen' => function($event) 
    {
        $event->sender->createCommand($q)->execute();

        /* @var $schema \neconix\yii2oci8\CachedSchema */
        $schema = $event->sender->getSchema();

        if (!$schema->isCached)
            //Rebuild schema cache
            $schema->buildSchemaCache();
    },