利用 PHP 反射自動生成 SQL CREATE TABLE 語句

以下程式示範如何結合 Reflection 機制，根據實體類別的註釋自動生成建表 SQL，展現反射在元資料驅動開發中的強大威力。

<?php
class PhpORM_Cli_GenerateSql {
    const MYSQL = 'mysql';
    const SQLITE = 'sqlite';

    protected $_hasLength = array('integer', 'varchar');
    protected $_regexes = array(
        'type'    => '/ type=([a-z_]*) /',
        'length'  => '/ length=([0-9]*) /',
        'default' => '/ default="(.*)" /',
        'null'    => '/ null /',
    );
    protected $_validTypes = array(
        'boolean'            => 'BOOL',
        'date'               => 'DATE',
        'integer'            => 'INT',
        'primary_autoincrement' => 'INT AUTO_INCREMENT PRIMARY KEY',
        'text'               => 'TEXT',
        'timestamp'          => 'TIMESTAMP',
        'varchar'            => 'VARCHAR',
    );
    protected $_className;
    protected $_tableName;
    protected $_type;

    public function __construct($class, $table_name, $type = self::MYSQL) {
        $this->_className = $class;
        $this->_tableName = $table_name;
        $this->_type = $type;
    }

    protected function _getDefinition($property) {
        preg_match($this->_regexes['type'], $property->getDocComment(), $matches);
        if (count($matches) == 2 && array_key_exists($matches[1], $this->_validTypes)) {
            $type = $this->_validTypes[$matches[1]];
            $length = in_array($matches[1], $this->_hasLength) ? $this->_getLength($property) : '';
            $null = ($matches[1] != 'primary_autoincrement') ? $this->_getNull($property) : '';
            return '`' . $property->getName() . '` ' . $type . ' ' . $length . ' ' . $null;
        }
        throw new Exception('Unsupported or missing type for property ' . $property->getName());
    }

    protected function _getLength($property) {
        preg_match($this->_regexes['length'], $property->getDocComment(), $matches);
        return (count($matches) == 2) ? '(' . $matches[1] . ')' : '';
    }

    protected function _getNull($property) {
        preg_match($this->_regexes['null'], $property->getDocComment(), $matches);
        return (count($matches) == 1) ? 'NULL' : 'NOT NULL';
    }

    public function getSql() {
        $class = new ReflectionClass($this->_className);
        $definitions = array();
        foreach ($class->getProperties() as $property) {
            if (strpos($property->getName(), '_') === false) { // 略過私有屬性
                $definitions[] = $this->_getDefinition($property);
            }
        }
        $columns = implode(",\n", $definitions);
        $sql = "CREATE TABLE " . $this->_tableName . " (" . $columns . ")";
        if ($this->_type == self::MYSQL) $sql .= " ENGINE=MYISAM";
        return $sql . ";";
    }
}
?>

使用方式

實體類別需在屬性文檔註釋中標註型別、長度、是否為空等元資料，例如：

class User {
    /** type=primary_autoincrement */
    protected $id;
    /** type=varchar length=50 null */
    protected $username;
    /** type=varchar length=100 */
    protected $email;
}

實例化生成器：

$generator = new PhpORM_Cli_GenerateSql('User', 'users');
echo $generator->getSql();

輸出：

CREATE TABLE users (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NULL,
    `email` VARCHAR(100) NOT NULL
) ENGINE=MYISAM;

此模式廣泛應用於 ORM 框架、代碼生成工具，實現「單一資料來源」原則。