PHP Classes

File: DATA/MySQL5/Schema.php

Recommend this page to a friend!
  Classes of Martin Alterisio   DATA   DATA/MySQL5/Schema.php   Download  
File: DATA/MySQL5/Schema.php
Role: Class source
Content type: text/plain
Description: MySQL5 schema observation functions class
Class: DATA
Access data stored in MySQL tables like arrays
Author: By
Last change: v0.8 - abstraction of sql data types and inboxing of these values into objects with constraints checking
Date: 17 years ago
Size: 11,231 bytes
 

Contents

Class file image Download
<?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; } } ?>