SqliteMetadata.php

<?php
/**
 * Zend Framework (http://framework.zend.com/)
 *
 * @link      http://github.com/zendframework/zf2 for the canonical source repository
 * @copyright Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)
 * @license   http://framework.zend.com/license/new-bsd New BSD License
 * @package   Zend_Db
 */

namespace Zend\Db\Metadata\Source;

use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSetInterface;

/**
 * @category   Zend
 * @package    Zend_Db
 * @subpackage Metadata
 */
class SqliteMetadata extends AbstractSource
{
    protected function loadSchemaData()
    {
        if (isset($this->data['schemas'])) {
            return;
        }
        $this->prepareDataHierarchy('schemas');

        $results = $this->fetchPragma('database_list');
        foreach ($results as $row) {
            $schemas[] = $row['name'];
        }
        $this->data['schemas'] = $schemas;
    }

    protected function loadTableNameData($schema)
    {
        if (isset($this->data['table_names'][$schema])) {
            return;
        }
        $this->prepareDataHierarchy('table_names', $schema);

        // FEATURE: Filename?

        $p = $this->adapter->getPlatform();

        $sql = 'SELECT "name", "type", "sql" FROM ' . $p->quoteIdentifierChain(array($schema, 'sqlite_master'))
             . ' WHERE "type" IN (\'table\',\'view\') AND "name" NOT LIKE \'sqlite_%\'';

        $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
        $tables = array();
        foreach ($results->toArray() as $row) {
            if ('table' == $row['type']) {
                $table = array(
                    'table_type' => 'BASE TABLE',
                    'view_definition' => null, // VIEW only
                    'check_option' => null,    // VIEW only
                    'is_updatable' => null,    // VIEW only
                );
            } else {
                $table = array(
                    'table_type' => 'VIEW',
                    'view_definition' => null,
                    'check_option' => 'NONE',
                    'is_updatable' => false,
                );

                // Parse out extra data
                if (null !== ($data = $this->parseView($row['sql']))) {
                    $table = array_merge($table, $data);
                }
            }
            $tables[$row['name']] = $table;
        }
        $this->data['table_names'][$schema] = $tables;
    }

    protected function loadColumnData($table, $schema)
    {
        if (isset($this->data['columns'][$schema][$table])) {
            return;
        }
        $this->prepareDataHierarchy('columns', $schema, $table);
        $this->prepareDataHierarchy('sqlite_columns', $schema, $table);

        $p = $this->adapter->getPlatform();


        $results = $this->fetchPragma('table_info', $table, $schema);

        $columns = array();

        foreach ($results as $row) {
            $columns[$row['name']] = array(
                // cid appears to be zero-based, ordinal position needs to be one-based
                'ordinal_position'          => $row['cid'] + 1,
                'column_default'            => $row['dflt_value'],
                'is_nullable'               => !((bool) $row['notnull']),
                'data_type'                 => $row['type'],
                'character_maximum_length'  => null,
                'character_octet_length'    => null,
                'numeric_precision'         => null,
                'numeric_scale'             => null,
                'numeric_unsigned'          => null,
                'erratas'                   => array(),
            );
            // TODO: populate character_ and numeric_values with correct info
        }

        $this->data['columns'][$schema][$table] = $columns;
        $this->data['sqlite_columns'][$schema][$table] = $results;
    }

    protected function loadConstraintData($table, $schema)
    {
        if (isset($this->data['constraints'][$schema][$table])) {
            return;
        }

        $this->prepareDataHierarchy('constraints', $schema, $table);

        $this->loadColumnData($table, $schema);
        $primaryKey = array();

        foreach ($this->data['sqlite_columns'][$schema][$table] as $col) {
            if ((bool)$col['pk']) {
                $primaryKey[] = $col['name'];
            }
        }

        if (empty($primaryKey)) {
            $primaryKey = null;
        }
        $constraints = array();
        $indexes = $this->fetchPragma('index_list', $table, $schema);
        foreach ($indexes as $index) {
            if (!((bool)$index['unique'])) {
                continue;
            }
            $constraint = array(
                'constraint_name' => $index['name'],
                'constraint_type' => 'UNIQUE',
                'table_name'      => $table,
                'columns'         => array(),
            );

            $info = $this->fetchPragma('index_info', $index['name'], $schema);

            foreach ($info as $column) {
                $constraint['columns'][] = $column['name'];
            }
            if ($primaryKey === $constraint['columns']) {
                $constraint['constraint_type'] = 'PRIMARY KEY';
                $primaryKey = null;
            }
            $constraints[$constraint['constraint_name']] = $constraint;
        }

        if (null !== $primaryKey) {
            $constraintName = '_zf_' . $table . '_PRIMARY';
            $constraints[$constraintName] = array(
                'constraint_name'  => $constraintName,
                'constraint_type'  => 'PRIMARY KEY',
                'table_name'       => $table,
                'columns' => $primaryKey,
            );
        }

        $foreignKeys = $this->fetchPragma('foreign_key_list', $table, $schema);

        $id = $name = null;
        foreach ($foreignKeys as $fk) {
            if ($id !== $fk['id']) {
                $id = $fk['id'];
                $name = '_zf_' . $table . '_FOREIGN_KEY_' . ($id + 1);
                $constraints[$name] = array(
                    'constraint_name'  => $name,
                    'constraint_type'  => 'FOREIGN KEY',
                    'table_name'       => $table,
                    'columns'          => array(),
                    'referenced_table_schema' => $schema,
                    'referenced_table_name'   => $fk['table'],
                    'referenced_columns'      => array(),
                    // TODO: Verify match, on_update, and on_delete values conform to SQL Standard
                    'match_option'     => strtoupper($fk['match']),
                    'update_rule'      => strtoupper($fk['on_update']),
                    'delete_rule'      => strtoupper($fk['on_delete']),
                );
            }
            $constraints[$name]['columns'][] = $fk['from'];
            $constraints[$name]['referenced_columns'][] = $fk['to'];
        }

        $this->data['constraints'][$schema][$table] = $constraints;
    }

    protected function loadTriggerData($schema)
    {
        if (isset($this->data['triggers'][$schema])) {
            return;
        }

        $this->prepareDataHierarchy('triggers', $schema);

        $p = $this->adapter->getPlatform();

        $sql = 'SELECT "name", "tbl_name", "sql" FROM '
             . $p->quoteIdentifierChain(array($schema, 'sqlite_master'))
             . ' WHERE "type" = \'trigger\'';

        $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
        $triggers = array();
        foreach ($results->toArray() as $row) {
            $trigger = array(
                'trigger_name'               => $row['name'],
                'event_manipulation'         => null, // in $row['sql']
                'event_object_catalog'       => null,
                'event_object_schema'        => $schema,
                'event_object_table'         => $row['tbl_name'],
                'action_order'               => 0,
                'action_condition'           => null, // in $row['sql']
                'action_statement'           => null, // in $row['sql']
                'action_orientation'         => 'ROW',
                'action_timing'              => null, // in $row['sql']
                'action_reference_old_table' => null,
                'action_reference_new_table' => null,
                'action_reference_old_row'   => 'OLD',
                'action_reference_new_row'   => 'NEW',
                'created'                    => null,
            );

            // Parse out extra data
            if (null !== ($data = $this->parseTrigger($row['sql']))) {
                $trigger = array_merge($trigger, $data);
            }
            $triggers[$trigger['trigger_name']] = $trigger;
        }

        $this->data['triggers'][$schema] = $triggers;
    }

    protected function fetchPragma($name, $value = null, $schema = null)
    {
        $p = $this->adapter->getPlatform();

        $sql = 'PRAGMA ';

        if (null !== $schema) {
            $sql .= $p->quoteIdentifier($schema) . '.';
        }
        $sql .= $name;

        if (null !== $value) {
            $sql .= '(' . $p->quoteValue($value) . ')';
        }

        $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
        if ($results instanceof ResultSetInterface) {
            return $results->toArray();
        }
        return array();
    }

    protected function parseView($sql)
    {
        static $re = null;
        if (null === $re) {
            $identifier = $this->getIdentifierRegularExpression();
            $identifierList = $this->getIdentifierListRegularExpression();
            $identifierChain = $this->getIdentifierChainRegularExpression();
            $re = $this->buildRegularExpression(array(
                'CREATE',
                array('TEMP|TEMPORARY'),
                'VIEW',
                array('IF','NOT','EXISTS'),
                $identifierChain,
                'AS',
                '(?<view_definition>.+)',
                array(';'),
            ));
        }

        if (!preg_match($re, $sql, $matches)) {
            return null;
        }
        return array(
            'view_definition' => $matches['view_definition'],
        );
    }

    protected function parseTrigger($sql)
    {
        static $re = null;
        if (null === $re) {
            $identifier = $this->getIdentifierRegularExpression();
            $identifierList = $this->getIdentifierListRegularExpression();
            $identifierChain = $this->getIdentifierChainRegularExpression();
            $re = $this->buildRegularExpression(array(
                'CREATE',
                array('TEMP|TEMPORARY'),
                'TRIGGER',
                array('IF','NOT','EXISTS'),
                $identifierChain,
                array('(?<action_timing>BEFORE|AFTER|INSTEAD\\s+OF)',),
                '(?<event_manipulation>DELETE|INSERT|UPDATE)',
                array('OF','(?<column_usage>' . $identifierList . ')'),
                'ON',
                '(?<event_object_table>' . $identifier . ')',
                array('FOR','EACH','ROW'),
                array('WHEN','(?<action_condition>.+)'),
                '(?<action_statement>BEGIN',
                '.+',
                'END)',
                array(';'),
            ));
        }

        if (!preg_match($re, $sql, $matches)) {
            return null;
        }
        $data = array();

        foreach ($matches as $key => $value) {
            if (is_string($key)) {
                $data[$key] = $value;
            }
        }

        // Normalize data and populate defaults, if necessary

        $data['event_manipulation'] = strtoupper($data['event_manipulation']);
        if (empty($data['action_condition'])) {
            $data['action_condition'] = null;
        }
        if (!empty($data['action_timing'])) {
            $data['action_timing'] = strtoupper($data['action_timing']);
            if ('I' == $data['action_timing'][0]) {
                // normalize the white-space between the two words
                $data['action_timing'] = 'INSTEAD OF';
            }
        } else {
            $data['action_timing'] = 'AFTER';
        }
        unset($data['column_usage']);

        return $data;
    }

    protected function buildRegularExpression(array $re)
    {
        foreach ($re as &$value) {
            if (is_array($value)) {
                $value = '(?:' . implode('\\s*+', $value) . '\\s*+)?';
            } else {
                $value .= '\\s*+';
            }
        }
        unset($value);
        $re = '/^' . implode('\\s*+', $re) . '$/';
        return $re;
    }

    protected function getIdentifierRegularExpression()
    {
        static $re = null;
        if (null === $re) {
            $re = '(?:' . implode('|', array(
                '"(?:[^"\\\\]++|\\\\.)*+"',
                '`(?:[^`]++|``)*+`',
                '\\[[^\\]]+\\]',
                '[^\\s\\.]+',
            )) . ')';
        }

        return $re;
    }

    protected function getIdentifierChainRegularExpression()
    {
        static $re = null;
        if (null === $re) {
            $identifier = $this->getIdentifierRegularExpression();
            $re = $identifier . '(?:\\s*\\.\\s*' . $identifier . ')*+';
        }
        return $re;
    }

    protected function getIdentifierListRegularExpression()
    {
        static $re = null;
        if (null === $re) {
            $identifier = $this->getIdentifierRegularExpression();
            $re = $identifier . '(?:\\s*,\\s*' . $identifier . ')*+';
        }
        return $re;
    }
}