Можно ли в миграции получить структуру таблицы?

Sereja3578 · Сообщение **Sereja3578** » 2017.08.13, 14:03

Всем хорошего времени суток)

Есть ли возможность в миграции получить данные о структуре таблиц чье имя удовлетворяет определенному регулярному выражению?

Есть 27 таблиц, нужно на их основе создать 27 других таблиц с префиксом в имени и дополнительными полями. Было бы замечательно если я мог бы сделать выборку из базы таблиц, которые содержат в имени _settings, получать в цикле их структуру, дополнять ее и пере-создавать через $this->createTable('prefix' . $name, $columns). Что-то в этом роде.

Возможно ли такое?

futbolim · Сообщение **futbolim** » 2017.08.13, 17:02

В чём проблема сделать копипастом 27 строк? Лучше на форум постить и терять время? )

zelenin · Сообщение **zelenin** » 2017.08.13, 19:36

Sereja3578 писал(а): ↑2017.08.13, 14:03 Всем хорошего времени суток)

Есть ли возможность в миграции получить данные о структуре таблиц чье имя удовлетворяет определенному регулярному выражению?

Есть 27 таблиц, нужно на их основе создать 27 других таблиц с префиксом в имени и дополнительными полями. Было бы замечательно если я мог бы сделать выборку из базы таблиц, которые содержат в имени _settings, получать в цикле их структуру, дополнять ее и пере-создавать через $this->createTable('prefix' . $name, $columns). Что-то в этом роде.

Возможно ли такое?

конечно да - вытаскиваем список таблиц, перебираем, сравнивая с регуляркой, с совпавшими проворачиваем описанную вами операцию.

Sereja3578 · Сообщение **Sereja3578** » 2017.08.14, 13:22

futbolim писал(а): ↑2017.08.13, 17:02 В чём проблема сделать копипастом 27 строк? Лучше на форум постить и терять время? )

Копипаст плохо. Тем более это не 27 строчек, а полная структура каждой таблицы, коих может быть больше 100. Каждую из которых надо по сути просто переименовать, дополнив префиксом и нужными полями. Копипастить запросы из конструктора тоже не хорошо так как изменись что в таблице, это не будет учтено. Значит надо из схемы брать данные при каждом билде и на основе них делать таблицы.

В начале хотел использовать SHOW COLUMNS, разбирать его и на основе этих данных формировать новые таблицы, дополняя их, но там нет даже комментариев к полям. Потом узнал что есть такая фишка как SHOW CREATE TABLE table_name, которая возвращает отличный код для создания таблицы, но почему-то выполняя этот запрос в миграции через $createTableCode = $this->execute('SHOW CREATE TABLE ' . $tableName); получаю null. Может туплю?

zelenin · Сообщение **zelenin** » 2017.08.14, 13:40

execute - это выполнение команды, query - это запрос данных

вообще в yii же есть встроенное получение схемы. посмотрите в эту сторону - оно может быть более удобно и ооп

tugrik · Сообщение **tugrik** » 2017.08.14, 17:55

Sereja3578 писал(а): ↑2017.08.14, 13:22 $this->execute('SHOW CREATE TABLE ' . $tableName);

Попробуйте :
Получить все имена таблиц:

Код: Выделить всё

$tableNames = Yii::$app->db->getSchema()->tableNames

Все схемы :

Код: Выделить всё

$schema = Yii::$app->db->schema->getTableSchema($tableName ,true);

Nex-Otaku · Сообщение **Nex-Otaku** » 2017.08.15, 10:49

Данные о схеме, через Yii можно получить, но не все. Имена ключей, кажется, нельзя вытащить, ещё что-то.

Поэтому для решения "дополнить и пересоздать" предлагаю сделать так.

1. Сделать полную копию таблицы.

2. Очистить данные в новой таблице.

3. Добавить нужные поля в новой таблице.

4. При необходимости - перенести данные из старой таблицы в новую.

Sereja3578 · Сообщение **Sereja3578** » 2017.08.27, 22:00

Если кому интересно будет вот миграция по созданию таблиц логов для всех таблиц с постфиксом _settings

Код: Выделить всё

<?php

use console\components\Migration;

class m170823_120938_add_settings_log_tables extends Migration
{

    const LOG_TABLE_POSTFIX = '_log';
    const LOG_TABLE_PK = 'logid';

    public function safeUp()
    {
        $tableNames = $this->db->schema->tableNames;
        foreach ($tableNames as $tableName) {
            if (preg_match('|_settings$|', $tableName)) {
                $this->addColumnsToSettingsTable($tableName);
                $createTableCode = $this->db->createCommand('SHOW CREATE TABLE ' . $tableName)->queryAll();

                $createLogTable = '';
                foreach ($createTableCode as $code) {
                    $createLogTable = preg_replace('#`(\w+_settings)`#', '`$1' . self::LOG_TABLE_POSTFIX . '`', $code['Create Table']);
                    $createLogTable = preg_replace('#`(\w+_settings)(-\w+)`#', '`$1' . self::LOG_TABLE_POSTFIX . '$2`', $createLogTable);
                    $createLogTable = preg_replace('#`(\w+-)(\w+_settings)(-\w+)`#', '`$1$2' . self::LOG_TABLE_POSTFIX . '$3`', $createLogTable);
                    $createLogTable = preg_replace('#NOT NULL#', 'NULL', $createLogTable);
                    $createLogTable = preg_replace('# AUTO_INCREMENT,#', ',', $createLogTable);
                    $createLogTable = preg_replace('#(\(\s)#', '$1 `' . self::LOG_TABLE_PK . '` int(10) unsigned NOT NULL AUTO_INCREMENT,', $createLogTable);
                    $createLogTable = preg_replace('#(AUTO_INCREMENT)=\d+#', '$1=0', $createLogTable);
                    $createLogTable = preg_replace('#PRIMARY KEY \(`.+`\),#', 'PRIMARY KEY (`' . self::LOG_TABLE_PK . '`),', $createLogTable);
                }

                // Создаем таблицы логов
                $this->execute($createLogTable);
                $this->addColumnsToLogTable($tableName);

                // Добавляем дополнительные поля к таблице логов
                $this->addForeignKeysToLogTable($tableName);
                // Вешаем триггер на таблицу логов
                $this->addTriggersToLogTable($tableName);

                // Вешаем триггеры на таблицы настроек
                $code = $this->getNewTriggerCode($tableName);
                $this->addTriggersToSettingsTable($tableName, $code);
            }
        }
    }

    /**
     * @param $tableName
     * @return string
     */
    protected function getNewTriggerCode ($tableName = 'app_currency_settings')
    {
        $customLogTableColumns = [
            'initiator',
            'user_agent',
            'ip',
            'http_host',
            'log_initiator_type_id',
        ];

        $settingsTableColumns = $this->db->getTableSchema($tableName)->getColumnNames();
        $settingsTableColumnsWithParams = $this->db->getTableSchema($tableName)->columns;

        // Проверка на иницитора изменения
        $isUpdateByAdminPanel = "
        IF (@LOG_INITIATOR_TYPE_ID IS NULL) 
        THEN 
            SET @LOG_INITIATOR_TYPE_ID = 3;
            SET @INITIATOR = USER();
        END IF;
        ";

        // Формируем условие для срабатывания insert только для важных обновленных полей
        $tableColumnsWithType = $this->db->getTableSchema($tableName)->columns;
        $insertCondition = [];
        foreach ($tableColumnsWithType as $columnName => $params) {
            if ($params->type != 'timestamp') {
                $insertCondition[] = $columnName;
            }
        }

        $insertCondition = join(' OR ', array_map(function ($columnName) {
                return 'NEW.' . $columnName . ' <> OLD.' . $columnName;
            }, array_values($insertCondition))
        );

        $ifBegin = ' IF (' . $insertCondition . ') THEN';
        $ifEnd = 'END IF;';

        // Тело инсерта
        $insertBegin =  'INSERT INTO `' . $tableName . self::LOG_TABLE_POSTFIX . '` SET ';

        /* Если поле было обновлено, присваиваем переменной с именем поля старое значение
        , если нет null. Если поле внешний ключ, всегда присваиваем старое значение */
        $insertSetIf = join(' ', array_map(function ($column) {
                if ($column->isForeignKey || $column->isPrimaryKey) {
                    return  'SET @' . $column->name . ' = OLD.' . $column->name . ';';
                }
                return  'IF (NEW.' . $column->name . ' <> OLD.' . $column->name . ') THEN SET @' . $column->name . ' = OLD.' . $column->name . '; ELSE SET @' . $column->name . ' = NULL; END IF;';
            }, array_values($settingsTableColumnsWithParams))
        );

        // Присваиваем полям значения переменных определенные в if
        $settingsTableColumnsSet = join(', ', array_map(function ($columnName) {
                return $columnName . ' = @' . $columnName;
            }, array_values($settingsTableColumns))
        );

        // Присвиваем кастомным полям значения переменных
        $customLogTableColumnsSet =  join(', ', array_map(function ($columnName) {
                    return $columnName . ' = @' . strtoupper($columnName);
                }, array_values($customLogTableColumns))
            );

        $insertSet =  $settingsTableColumnsSet . ', ' . $customLogTableColumnsSet;

        // Результирующий код триггера
        $code = $ifBegin . $isUpdateByAdminPanel . $insertSetIf . $insertBegin . $insertSet . ';' . $ifEnd;

        return $code;
    }

    /**
     * @param $tableName
     * @param $code
     */
    protected function addTriggersToSettingsTable ($tableName, $code) {
        $options = [
            '' . $tableName . '' => [
                'expression' => "`Table` = 'app_currency_payin_settings' and `Event` = 'UPDATE' and `Timing` = 'AFTER'",
                'newPartOfTrigger' => ['place' => 'afterOldPart', 'code' => $code],
                'triggerEvent' => 'AFTER UPDATE'
            ]
        ];

        $this->updateTrigger($options, $drop = true);
    }

    protected function addTriggersToLogTable ($tableName) {
        $this->createTrigger(
            $tableName . self::LOG_TABLE_POSTFIX,
            $tableName . self::LOG_TABLE_POSTFIX . "_BEFORE_DELETE",
            /** @lang SQL */
            <<<SQL
SIGNAL SQLSTATE VALUE '03999'
SET MESSAGE_TEXT = 'Delete operations are restricted.', MYSQL_ERRNO = 999;
SQL
            ,
            'BEFORE DELETE'
        );

        $this->createTrigger(
            $tableName . self::LOG_TABLE_POSTFIX,
            $tableName . self::LOG_TABLE_POSTFIX . "_BEFORE_UPDATE",
            /** @lang SQL */
            <<<SQL
SIGNAL SQLSTATE VALUE '03999'
SET MESSAGE_TEXT = 'Update operations are restricted.', MYSQL_ERRNO = 998;
SQL
            ,
            'BEFORE UPDATE'
        );

        $this->createTrigger(
          $tableName . self::LOG_TABLE_POSTFIX,
            $tableName . self::LOG_TABLE_POSTFIX . "_AFTER_INSERT",
            "
INSERT INTO `change_log` SET idlog = NEW.logid, `settings_table_name` = '" . $tableName . self::LOG_TABLE_POSTFIX . "';",
          'AFTER INSERT'
        );
    }

    /**
     * @param $tableName
     */
    protected function addColumnsToLogTable ($tableName)
    {
        $this->addColumn(
            $tableName . self::LOG_TABLE_POSTFIX,
            'initiator',
            $this->string(255)->comment('Версия браузера')->null()
        );
        $this->addColumn(
            $tableName . self::LOG_TABLE_POSTFIX,
            'log_initiator_type_id',
            $this->integer(10)->unsigned()->comment('Тип инициатора')->null()
        );
        $this->addColumn(
            $tableName . self::LOG_TABLE_POSTFIX,
            'user_agent',
            $this->string(255)->comment('Версия браузера')->null()
        );
        $this->addColumn(
            $tableName . self::LOG_TABLE_POSTFIX,
            'ip',
            $this->string(15)->comment('ip компьютера')->null()
        );
        $this->addColumn(
            $tableName . self::LOG_TABLE_POSTFIX,
            'http_host',
            $this->string(255)->comment('Хост')->null()
        );
    }

    /**
     * @param $tableName
     */
    public function addForeignKeysToLogTable ($tableName) {
        $this->addForeignKey('fk_' . $tableName . self::LOG_TABLE_POSTFIX . '-log_initiator_type_id', $tableName . self::LOG_TABLE_POSTFIX, ['log_initiator_type_id'], 'log_initiator_type', ['id']);
    }

    /**
     * @param $tableName
     */
    protected function addColumnsToSettingsTable ($tableName)
    {
        if (!$this->hasColumn($tableName, 'created_at')) {
            $this->addColumn(
                $tableName,
                'created_at',
                $this->timestamp()->comment('Время создания')->notNull()->defaultExpression('CURRENT_TIMESTAMP')
            );
        }

        if (!$this->hasColumn($tableName, 'updated_at')) {
            $this->addColumn(
                $tableName,
                'updated_at',
                $this->updatedAtShortcut()
            );
        }
    }

    public function safeDown()
    {
        $tableNames = $this->db->schema->tableNames;
        foreach ($tableNames as $tableName) {
            if (preg_match('|_settings_log$|', $tableName)) {
                $this->dropTable($tableName);
            }
        }
    }
}

Это код создания общей таблицы логов (куда триггером кидаются изменения со всех таблиц)

Код: Выделить всё

<?php

use console\components\Migration;

class m170823_120937_create_change_log_table extends Migration
{

    const TABLE_NAME = 'change_log';

    public function safeUp()
    {
        $this->createTable(self::TABLE_NAME, [
            'idlog' => $this->integer(10)->notNull()->unsigned()->comment('id записи из лоигруемой таблицы'),
            'log_table_name' => $this->string(255)->notNull()->comment('Название таблицы лога'),
            'timestamp' => $this->timestamp('CURRENT_TIMESTAMP')->notNull()->comment('Время изменения')
        ]);

        $this->addPrimaryKey('pk_'. self::TABLE_NAME . '-idlog-settings_table_name', self::TABLE_NAME, [
            'idlog',
            'settings_table_name'
        ]);

        $this->createTrigger(
            self::TABLE_NAME,
            self::TABLE_NAME . "_BEFORE_DELETE",
            /** @lang SQL */
            <<<SQL
SIGNAL SQLSTATE VALUE '03999'
SET MESSAGE_TEXT = 'Delete operations are restricted.', MYSQL_ERRNO = 999;
SQL
            ,
            'BEFORE DELETE'
        );

        $this->createTrigger(
            self::TABLE_NAME,
            self::TABLE_NAME . "_BEFORE_UPDATE",
            /** @lang SQL */
            <<<SQL
SIGNAL SQLSTATE VALUE '03999'
SET MESSAGE_TEXT = 'Update operations are restricted.', MYSQL_ERRNO = 998;
SQL
            ,
            'BEFORE UPDATE'
        );
    }

    public function safeDown()
    {
        $this->dropTable(self::TABLE_NAME);
    }
}

Тип инициатора выставляется в ActiveRecord save и в миграции up. Если не выставлен, значит база данных.

Yii Framework

Можно ли в миграции получить структуру таблицы?

Можно ли в миграции получить структуру таблицы?

Re: Можно ли в миграции получить структуру таблицы?

Re: Можно ли в миграции получить структуру таблицы?

Re: Можно ли в миграции получить структуру таблицы?

Re: Можно ли в миграции получить структуру таблицы?

Re: Можно ли в миграции получить структуру таблицы?

Re: Можно ли в миграции получить структуру таблицы?

Re: Можно ли в миграции получить структуру таблицы?