/Classes/Utility/DynamicConfiguration/SQL.php

<?php
/***************************************************************
 *  Copyright notice
 *
 *  (c) 2016 IMIA net based solutions (info@imia.de)
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
 *  free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  The GNU General Public License can be found at
 *  http://www.gnu.org/copyleft/gpl.html.
 *
 *  This script is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  This copyright notice MUST APPEAR in all copies of the script!
 ***************************************************************/

namespace IMIA\ImiaBaseExt\Utility\DynamicConfiguration;

use IMIA\ImiaBaseExt\Metadata\ClassMetadata;
use IMIA\ImiaBaseExt\Metadata\PropertyMetadata;
use TYPO3\CMS\Core\Utility\GeneralUtility;

/**
 * @package     imia_base_ext
 * @subpackage  Utility\DynamicConfiguration
 * @author      David Frerich <d.frerich@imia.de>
 */
class SQL
{
    /**
     * @var string
     */
    private $extKey;

    /**
     * @param string $extKey
     * @param ClassMetadata $classMetadata
     * @return string
     */
    static public function getTableName($extKey, $classMetadata)
    {
        if ($classMetadata->sqlTable && $classMetadata->sqlTable->name) {
            $tableName = $classMetadata->sqlTable->name;
        } else {
            $className = substr($classMetadata->name, strrpos($classMetadata->name, '\\') !== false ? strrpos($classMetadata->name, '\\') + 1 : 0);
            $tableName = 'tx_' . str_replace('_', '', $extKey) . '_domain_model_' . GeneralUtility::camelCaseToLowerCaseUnderscored($className);
        }

        return $tableName;
    }

    /**
     * @param PropertyMetadata $propertyMetadata
     * @return string
     */
    static public function getColumnName($propertyMetadata)
    {
        if ($propertyMetadata->sqlColumn && $propertyMetadata->sqlColumn->name) {
            $name = $propertyMetadata->sqlColumn->name;
        } else {
            $name = GeneralUtility::camelCaseToLowerCaseUnderscored($propertyMetadata->name);
        }

        return $name;
    }

    /**
     * @param string $extKey
     */
    public function __construct($extKey)
    {
        $this->extKey = $extKey;
    }

    /**
     * @param ClassMetadata $metadata
     * @return string
     */
    public function build($metadata)
    {
        $sql = '### EXTENSION: ' . $this->extKey . " ###";
        foreach ($metadata as $className => $classMetadata) {
            if ($classMetadata->sqlTable && !$classMetadata->reflection->isAbstract()) {
                if (!$classMetadata->tcaTable || !$classMetadata->tcaTable->ignore) {
                    $tableName = self::getTableName($this->extKey, $classMetadata);

                    $columnSql = $this->buildColumns($classMetadata);
                    if ($columnSql) {
                        $sql .=
                            LF .
                            LF .
                            "## CLASS: " . $className . LF .
                            "# Table structure for table '" . $tableName . "'" . LF .
                            "CREATE TABLE `" . $tableName . "` (" . LF .
                            $columnSql .
                            ");";
                    }
                }
            }

            // MM Tables
            if ($classMetadata->tcaTable && !$classMetadata->tcaTable->ignore && !$classMetadata->reflection->isAbstract()) {
                foreach ($classMetadata->propertyMetadata as $property => $propertyMetadata) {
                    $column = $propertyMetadata->tcaColumn;
                    if ($column) {
                        $type = $column::$fixed['type'] ?: $column->type;
                        if (in_array($type, ['select', 'group'])) {
                            $mmTableName = $column::$fixed['MM'] ?: $column->MM;
                            $mmOppositeField = $column::$fixed['MM_opposite_field'] ?: $column->MM_opposite_field;

                            if ($mmTableName && $mmOppositeField) {
                                $sql .=
                                    LF .
                                    LF .
                                    "## CLASS: " . $className . " | MM PROPERTY:  " . $property . LF .
                                    "# Table structure for table '" . $mmTableName . "'" . LF .
                                    "CREATE TABLE `" . $mmTableName . "` (" . LF .
                                    "   uid int(11) NOT NULL auto_increment," . LF .
                                    "   uid_local int(11) DEFAULT '0' NOT NULL," . LF .
                                    "   uid_foreign int(11) DEFAULT '0' NOT NULL," . LF .
                                    "   sorting int(11) DEFAULT '0' NOT NULL," . LF .
                                    "   sorting_foreign int(11) DEFAULT '0' NOT NULL," . LF .
                                    "   KEY uid_local (uid_local)," . LF .
                                    "   KEY uid_foreign (uid_foreign)," . LF .
                                    "   PRIMARY KEY (uid)," . LF .
                                    ");";
                            }
                        }
                    }
                }
            }
        }
        $sql .= LF . LF;

        return $sql;
    }

    /**
     * @param ClassMetadata $metadata
     * @return string
     */
    private function buildColumns($metadata)
    {
        $sql = '';
        $keys = [];
        $uniqueKeys = [];
        $primaryKey = '';

        foreach ($metadata->propertyMetadata as $property => $propertyMetadata) {
            $column = $propertyMetadata->sqlColumn;

            if ($column && $column->create) {
                $name = self::getColumnName($propertyMetadata);
                $length = (int)$column->length;
                $decimal = (int)$column->decimal;
                $nullable = (boolean)$column->nullable;
                $unsigned = (boolean)$column->unsigned;
                $autoIncrement = (boolean)$column->autoIncrement;
                $default = (string)$column->default;
                $type = $column->type;

                if (!$primaryKey && (boolean)$column->primaryKey) {
                    $primaryKey = $name;
                }
                if ($column->key) {
                    $keyName = $column->key;
                    if (!array_key_exists($keyName, $keys)) {
                        $keys[$keyName] = [];
                    }
                    $keys[$keyName][] = $name;
                }
                if ($column->unique) {
                    $keyName = $column->key ? $name : $name;
                    if (!array_key_exists($keyName, $uniqueKeys)) {
                        $uniqueKeys[$keyName] = [];
                    }
                    $uniqueKeys[$keyName][] = $name;
                }

                switch ($type) {
                    case 'char':
                    case 'varchar':
                        $sql .= '    `' . $name . '` ' . $type . '(' . ($length <= 255 ? $length : 255) . ') DEFAULT ' . ($default ? '\'' . str_replace(["'", '"'], ["\'", '\"'], $default) . '\'' : ($nullable ? 'NULL' : '\'\'')) . ($nullable ? '' : ' NOT NULL') . ',' . LF;
                        break;
                    case 'int':
                    case 'tinyint':
                    case 'smallint':
                    case 'mediumint':
                    case 'bigint':
                        $sql .= '    `' . $name . '` ' . $type . '(' . ($length <= 11 ? $length : 11) . ')' . ($unsigned ? ' unsigned' : '') . ($autoIncrement ? '' : ' DEFAULT ' . ($default ? '\'' . (int)$default . '\'' : ($nullable ? 'NULL' : '\'0\''))) . ($nullable ? '' : ' NOT NULL') . ($autoIncrement ? ' auto_increment' : '') . ',' . LF;
                        break;
                    case 'text':
                    case 'tinytext':
                    case 'mediumtext':
                    case 'longtext':
                        $sql .= '    `' . $name . '` ' . $type . ' ' . ($nullable ? '' : 'NOT NULL') . ',' . LF;
                        break;
                    case 'float':
                    case 'double':
                    case 'real':
                    case 'decimal':
                        $sql .= '    `' . $name . '` ' . $type . '(' . $length . ',' . $decimal . ') DEFAULT ' . ($default && (float)$default != 0 ? '\'' . (float)$default . '\'' : ($nullable ? 'NULL' : '\'0' . ($decimal ? '.' . str_pad('', $decimal, '0') : '') . '\'')) . ($nullable ? '' : ' NOT NULL') . ',' . LF;
                        break;
                    case 'date':
                    case 'time':
                        $sql .= '    `' . $name . '` ' . $type . ',' . LF;
                        break;
                    case 'blob':
                    case 'tinyblob':
                    case 'mediumblob':
                    case 'longblob':
                        $sql .= '    `' . $name . '` ' . $type . ',' . LF;
                        break;
                }
            }
        }

        if ($metadata->sqlIndex) {
            if ($metadata->sqlIndex->keys && is_array($metadata->sqlIndex->keys)
                && count($metadata->sqlIndex->keys) > 0
            ) {
                foreach ($metadata->sqlIndex->keys as $keyName => $columns) {
                    $keys[$keyName] = $columns;
                }
            }
            if ($metadata->sqlIndex->uniqueKeys && is_array($metadata->sqlIndex->uniqueKeys)
                && count($metadata->sqlIndex->uniqueKeys) > 0
            ) {
                foreach ($metadata->sqlIndex->uniqueKeys as $keyName => $columns) {
                    $uniqueKeys[$keyName] = $columns;
                }
            }
        }

        if ($sql) {
            if ($primaryKey || count($keys) > 0 || count($uniqueKeys) > 0) {
                $sql .= LF;
                if ($primaryKey) {
                    $sql .= '    PRIMARY KEY (`' . $primaryKey . '`),' . LF;
                }
                foreach ($keys as $keyName => $columns) {
                    $sql .= '    KEY `' . $keyName . '` (`' . implode('`,`', $columns) . '`),' . LF;
                }
                foreach ($uniqueKeys as $keyName => $columns) {
                    $sql .= '    UNIQUE `' . $keyName . '` (`' . implode('`,`', $columns) . '`),' . LF;
                }
            }

            $sql = substr($sql, 0, -2) . LF;
        }

        return $sql;
    }
}