File: DATA/MySQL5/Schema.php

<?php
/**
 * @package DATA_MySQL5
 */

/**
 * This class provides information about the current schema in
 * a MySQL5 database.
 * 
 * Field types returned are those provided by MySQL:
 *   int,
 *   varchar,
 *   text,
 *   ...etc
 */
class DATA_MySQL5_Schema {
    /**
     * Cached results of primary keys information.
     * @var array
     */
    static protected $primaryKeys;

    /**
     * Cached results of fields information.
     * @var array
     */
    static protected $fields;
    
    /**
     * Cached results of fields with unique indexes information.
     * @var array
     */
    static protected $uniqueFields;
    
    /**
     * Cached results of field definition information.
     * @var array
     */
    static protected $fieldDefs;
    
    /**
     * SQL Type Factories instanciated.
     * @var array
     */
    static protected $sqlTypeFactories;
    
    /**
     * Cached results of auto-increment fields information.
     * @var array
     */
    static protected $autoIncrementFields;
    
    /**
     * Returns the primary key of the indicated table.
     * @param string $table The table we would like to know its primary key.
     * @return array An array of the columns that compose the primary key.
     */
    static public function getPrimaryKey($table) {
        if (isset(self::$primaryKeys[$table])) return self::$primaryKeys[$table];
        $query = DATA_MySQL5_Access::query("
            SELECT column_name
              FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = '" . DATA_MySQL5_Access::escape($table) . "'
               AND column_key = 'PRI'
        ");
        $keys = array();
        while (list($columnName) = DATA_MySQL5_Access::fetchRow($query)) {
            $keys[] = $columnName;
        }
        self::$primaryKeys[$table] = $keys;
        return $keys;
    }
    
    /**
     * Indicates if the specified field is part of the primary key.
     * @param string $table The table name.
     * @param string $field The field.
     * @return bool True if part of the primary key.
     */
    public function isPrimaryKey($table, $field) {
        return in_array($field, self::getPrimaryKey($table));
    }
    
    /**
     * Returns the fields of the indicated table.
     * @param string $table The table we would like to know its fields.
     * @return array An array with the name of the fields.
     */
    static public function getFields($table) {
        if (isset(self::$fields[$table])) return self::$fields[$table];
        $query = DATA_MySQL5_Access::query("
            SELECT column_name
              FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = '" . DATA_MySQL5_Access::escape($table) . "'
        ");
        $fields = array();
        while (list($columnName) = DATA_MySQL5_Access::fetchRow($query)) {
            $fields[] = $columnName;
        }
        self::$fields[$table] = $fields;
        return $fields;
    }
    
    /**
     * Returns the unique indexed fields of the indicated table.
     * @param string $table The table we would like to know its unique indexed fields.
     * @return array An array with the name of the unique indexed fields.
     */
    static public function getUniqueFields($table) {
        if (isset(self::$uniqueFields[$table])) return self::$uniqueFields[$table];
        $query = DATA_MySQL5_Access::query("
            SELECT column_name
              FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = '" . DATA_MySQL5_Access::escape($table) . "'
               AND column_key = 'UNI'
        ");
        $fields = array();
        while (list($columnName) = DATA_MySQL5_Access::fetchRow($query)) {
            $fields[] = $columnName;
        }
        self::$uniqueFields[$table] = $fields;
        return $fields;
    }
    
    /**
     * Returns the type of a field.
     * @param string $table The table name.
     * @param string $field The field.
     * @return string A string representation of the field type.
     */
    static public function getFieldType($table, $field) {
        $fieldDefinition = self::getFieldDefinition($table, $field);
        return $fieldDefinition['data_type'];
    }
    
    /**
     * Returns the size of a character field.
     * @param string $table The table name.
     * @param string $field The field.
     * @return int The size of the character field.
     */
    static public function getCharFieldSize($table, $field) {
        $fieldDefinition = self::getFieldDefinition($table, $field);
        return (int)$fieldDefinition['character_maximum_length'];
    }
    
    /**
     * Returns the precision of a numeric field.
     * @param string $table The table name.
     * @param string $field The field.
     * @return int The precision of the numeric field.
     */
    static public function getNumericFieldPrecision($table, $field) {
        $fieldDefinition = self::getFieldDefinition($table, $field);
        return (int)$fieldDefinition['numeric_precision'];
    }
    
    /**
     * Returns the scale of a numeric field.
     * @param string $table The table name.
     * @param string $field The field.
     * @return int The scale of the numeric field.
     */
    static public function getNumericFieldScale($table, $field) {
        $fieldDefinition = self::getFieldDefinition($table, $field);
        return (int)$fieldDefinition['numeric_scale'];
    }
    
    /**
     * Indicates if a field is an auto-incremented index.
     * 
     * @param string $table The table name.
     * @param string $field The field.
     * @return boolean True if the field is an auto-incremented index.
     */
    static public function isAutoIncrement($table, $field) {
        $fieldDefinition = self::getFieldDefinition($table, $field);
        return $fieldDefinition['extra'] == 'auto_increment';
    }
    
    /**
     * Indicates if a field is nullable.
     * 
     * @param string $table The table name.
     * @param string $field The field.
     * @return boolean True if the field is nullable.
     */
    static public function isNullable($table, $field) {
        if (self::isAutoIncrement($table, $field)) return true;
        $fieldDefinition = self::getFieldDefinition($table, $field);
        return $fieldDefinition['is_nullable'] == 'YES';
    }
    
    /**
     * Returns internal data regarding the definition of a field.
     * @param string $table The table name.
     * @param string $field The field.
     * @return array Values from the columns definition table.
     */
    static protected function getFieldDefinition($table, $field) {
        if (isset(self::$fieldDefs[$table][$field])) return self::$fieldDefs[$table][$field];
        $query = DATA_MySQL5_Access::query("
            SELECT is_nullable, data_type, character_maximum_length, numeric_precision, numeric_scale, extra
              FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = '" . DATA_MySQL5_Access::escape($table) . "'
               AND column_name = '" . DATA_MySQL5_Access::escape($field) . "'
        ");
        self::$fieldDefs[$table][$field] = DATA_MySQL5_Access::fetchAssoc($query);
        return self::$fieldDefs[$table][$field];
    }
    
    /**
     * Returns the auto-incremented field of a table.
     * 
     * @param string $table The table name.
     * @return string The auto-incremented field.
     */
    static public function getAutoIncrementField($table) {
        if (isset(self::$autoIncrementFields[$table])) return self::$autoIncrementFields[$table];
        $query = DATA_MySQL5_Access::query("
            SELECT column_name
              FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = '" . DATA_MySQL5_Access::escape($table) . "'
               AND extra = 'auto_increment'
        ");
        list($field) = DATA_MySQL5_Access::fetchRow($query);
        self::$autoIncrementFields[$table] = $field;
        return $field;
    }
    
    /**
     * Returns an internal hash identifying the sql data type of the field.
     * @param string $table The table name.
     * @param string $field The field.
     * @return string Internal hash.
     */
    static protected function getFieldTypeHash($table, $field) {
        $nullable = self::isNullable($table, $field) ? 'null,' : '';
        $fieldType = self::getFieldType($table, $field);
        switch ($fieldType) {
        case 'char':
        case 'varchar':
            return $nullable . $fieldType . ',' . self::getCharFieldSize($table, $field);
        case 'decimal':
            return $nullable . $fieldType . ',' . self::getNumericFieldPrecision($table, $field)
                                          . ',' . self::getNumericFieldScale($table, $field);
        default:
            return $nullable . $fieldType;
        }
    }
    
    /**
     * Returns the factory used to create sql data types for the specified field.
     * 
     * @param string $table The table name.
     * @param string $field The field.
     * @return DATA_SQLTypeFactory SQL data type factory.
     */
    static public function getSQLTypeFactory($table, $field) {
        $fieldHash = self::getFieldTypeHash($table, $field);
        $nullable = self::isNullable($table, $field);
        if (isset(self::$sqlTypeFactories[$fieldHash])) return self::$sqlTypeFactories[$fieldHash];
        switch (self::getFieldType($table, $field)) {
        case 'char':
            $factory = new DATA_SQLCharFactory($nullable, self::getCharFieldSize($table, $field));
            break;
        case 'varchar':
            $factory = new DATA_SQLVarcharFactory($nullable, self::getCharFieldSize($table, $field));
            break;
        case 'int':
            $factory = new DATA_SQLIntFactory($nullable);
            break;
        case 'smallint':
            $factory = new DATA_SQLSmallIntFactory($nullable);
            break;
        case 'decimal':
            $factory = new DATA_SQLDecimalFactory($nullable, self::getNumericFieldPrecision($table, $field),
                                                             self::getNumericFieldScale($table, $field));
            break;
        case 'date':
            $factory = new DATA_MySQL5_SQLDateFactory($nullable);
            break;
        case 'time':
            $factory = new DATA_MySQL5_SQLTimeFactory($nullable);
            break;
        case 'datetime':
            $factory = new DATA_MySQL5_SQLDatetimeFactory($nullable);
            break;
        default:
            throw new DATA_NotImplemented("Factory for field of type '" . self::getFieldType($table, $field) . "'");
            break;
        }
        self::$sqlTypeFactories[$fieldHash] = $factory;
        return $factory;
    }
}
?>