Phalcon use Oracle views

Question

In a Phalcon project, I have multiple database in Oracle and mySQL with number of tables and views. Created models for corresponding tables and views. But unable to access views. I initialize in model below:

public function initialize()
    {
        $this->setConnectionService('dbBznes');
        $this->setSchema('POLICY');
        $this->setSource("BUSINESS_ALL");
    }

Answer 1

As per the comments, this is apparently a metadata issue and the default metadata strategy is introspection and is attempting to check if the table exists. You can set up your own metadata strategy like so:

$di['modelsMetadata'] = function()
{
    $metadata = new \Phalcon\Mvc\Model\MetaData\Memory();
    $metadata->setStrategy(
        new MyIntrospectionStrategy()
    );

    return $metadata;
};

"Memory" in this case means don't use any sort of metadata caching. This goes off into another tangent as you can cache in many ways for more speed in production, etc.

As for the MyIntrospectionStrategy class above, it represents your own class based on Phalcon's Introspection strategy which attempts to analyze the database to figure out the fields and their types involved with the table.

I believe I converted Phalcon\Mvc\Model\MetaData\Strategy\Introspection from Zephir to PHP correctly as follows:

class MyIntrospectionStrategy implements \Phalcon\Mvc\Model\MetaData\StrategyInterface
{

    public final function getMetaData(\Phalcon\Mvc\ModelInterface $model, \Phalcon\DiInterface $dependencyInjector)
    {
        $schema = $model->getSchema();
        $table = $model->getSource();

        $readConnection = $model->getReadConnection();



        if( !$readConnection->tableExists($table, $schema) )
        {
            if($schema)
            {
                $completeTable = $schema . "'.'" . $table;
            } else {
                $completeTable = $table;
            }

            throw new \Phalcon\Mvc\Model\Exception(
                "Table '" . $completeTable . "' doesn't exist in database when dumping meta-data for " . get_class($model)
            );
        }



        $columns = $readConnection->describeColumns($table, $schema);

        if( !count($columns) )
        {

            if($schema)
            {
                $completeTable = $schema . "'.'" . $table;
            } else {
                $completeTable = $table;
            }

            /**
             * The table not exists
             */
            throw new \Phalcon\Mvc\Model\Exception(
                "Cannot obtain table columns for the mapped source '" . completeTable . "' used in model " . get_class(model)
            );
        }



        $attributes = [];
        $primaryKeys = [];
        $nonPrimaryKeys = [];
        $numericTyped = [];
        $notNull = [];
        $fieldTypes = [];
        $fieldBindTypes = [];
        $automaticDefault = [];
        $identityField = false;
        $defaultValues = [];
        $emptyStringValues = [];

        foreach($columns as $column)
        {

            $fieldName = $column->getName();
            $attributes[] = $fieldName;

            if ($column->isPrimary() === true)
            {
                $primaryKeys[] = $fieldName;
            } else {
                $nonPrimaryKeys[] = $fieldName;
            }

            if ($column->isNumeric() === true)
            {
                $numericTyped[$fieldName] = true;
            }

            if ($column->isNotNull() === true)
            {
                $notNull[] = $fieldName;
            }

            if ($column->isAutoIncrement() === true)
            {
                $identityField = $fieldName;
            }

            $fieldTypes[$fieldName] = $column->getType();

            $fieldBindTypes[$fieldName] = $column->getBindType();

            $defaultValue = $column->getDefault();
            if ($defaultValue !== null || $column->isNotNull() === false)
            {
                if ( !$column->isAutoIncrement() )
                {
                    $defaultValues[$fieldName] = $defaultValue;
                }
            }
        }

        return [
            \Phalcon\Mvc\Model\MetaData::MODELS_ATTRIBUTES               => $attributes,
            \Phalcon\Mvc\Model\MetaData::MODELS_PRIMARY_KEY              => $primaryKeys,
            \Phalcon\Mvc\Model\MetaData::MODELS_NON_PRIMARY_KEY          => $nonPrimaryKeys,
            \Phalcon\Mvc\Model\MetaData::MODELS_NOT_NULL                 => $notNull,
            \Phalcon\Mvc\Model\MetaData::MODELS_DATA_TYPES               => $fieldTypes,
            \Phalcon\Mvc\Model\MetaData::MODELS_DATA_TYPES_NUMERIC       => $numericTyped,
            \Phalcon\Mvc\Model\MetaData::MODELS_IDENTITY_COLUMN          => $identityField,
            \Phalcon\Mvc\Model\MetaData::MODELS_DATA_TYPES_BIND          => $fieldBindTypes,
            \Phalcon\Mvc\Model\MetaData::MODELS_AUTOMATIC_DEFAULT_INSERT => $automaticDefault,
            \Phalcon\Mvc\Model\MetaData::MODELS_AUTOMATIC_DEFAULT_UPDATE => $automaticDefault,
            \Phalcon\Mvc\Model\MetaData::MODELS_DEFAULT_VALUES           => $defaultValues,
            \Phalcon\Mvc\Model\MetaData::MODELS_EMPTY_STRING_VALUES      => $emptyStringValues
        ];

    }


    public final function getColumnMaps(\Phalcon\Mvc\ModelInterface $model, \Phalcon\DiInterface $dependencyInjector)
    {
        $orderedColumnMap = null;
        $reversedColumnMap = null;

        if (method_exists($model, 'columnMap'))
        {

            $userColumnMap = $model->columnMap();
            if ( gettype($userColumnMap) != 'array')
            {
                // Bad grammer directly in cphalcon :sadface:
                throw new \Phalcon\Mvc\Model\Exception('columnMap() not returned an array');
            }

            $reversedColumnMap = [];
            $orderedColumnMap = $userColumnMap;
            foreach($userColumnMap as $name => $userName)
            {
                $reversedColumnMap[$userName] = $name;
            }
        }

        return [$orderedColumnMap, $reversedColumnMap];
    }


}

I have not tested this.

As far as adding support for views to be treated like tables, the change might be as simple as:

Before:

if( !$readConnection->tableExists($table, $schema) )

After:

if( !$readConnection->tableExists($table, $schema) && !$readConnection->viewExists($table, $schema) )

If this doesn't work due to logic choking with describeColumns, you might need to write something specific for working with views in Oracle for this dialect.

As far as other solutions, you can provide your own metadata method directly on the model by specifying your ownmetaData method directly on it.

Another solution is to use annotations instead of introspection for metadata. Then you'd place your metadata as comments in the code for Phalcon to parse.

If you continue to run into problems with Database Views, just run it as raw SQL rather than attempting to use the ORM to do it. You can simply define a new method on your model to run the raw SQL.