Нужна помощь с MySQL запросом при установке плагина

[ravchik]

Доброго врмени.

Пишу плагин, при установке которго должен добавляться столбец region_id в таблицу dle_post.
При первичной установке код ALTER TABLE `{prefix}_post` ADD `region_id` VARCHAR(190) DEFAULT NULL; работает нормально. Но если этот столбец уже добавлен ранее, то возникает ошибка, что столбец уже существует.

Пробовал такие варианты:

ALTER TABLE `{prefix}_post` ADD COLUMN IF NOT EXISTS `region_id` VARCHAR(190) DEFAULT NULL;

SELECT COUNT(*) FROM information_schema.columns WHERE table_name = '{prefix}_post' AND column_name = 'region_id';
ALTER TABLE `{prefix}_post` ADD `region_id` VARCHAR(190) DEFAULT NULL;

но они не помогли.
Удалять столбец region_id при удалении плагина нельзя.
Подскажите, как можно решить проблему?

 

[MSK]

Перенеси в раздел PHP с проверкой существования поля...

 

[MaHarder]

create or replace procedure addFieldIfNotExists(IN table_name_IN varchar(100), IN field_name_IN varchar(100),
                                                IN field_definition_IN varchar(100))
BEGIN

    SET @isFieldThere = isFieldExisting(table_name_IN, field_name_IN);
    IF (@isFieldThere = 0) THEN

        SET @ddl = CONCAT('ALTER TABLE ', table_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN');
        SET @ddl = CONCAT(@ddl, ' ', field_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', field_definition_IN);

        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END IF;

END;

create or replace function isFieldExisting(table_name_IN varchar(100), field_name_IN varchar(100)) returns int
    RETURN (
        SELECT COUNT(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.columns
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = table_name_IN
          AND COLUMN_NAME = field_name_IN
    );

вот процедура, не знаю - будет ли она работать на новых версиях мускула. Добавлял в некоторых релизах своей разработки дле апи.

пример использования.

CALL addFieldIfNotExists ('{prefix}_api_keys', 'own_only', 'boolean default false not null');

на постгре используется немного инной синтакс, чем в мускуле, поэтому проверяй сам - вызывается ли функционал или нет

 

[ravchik]

Перенеси в раздел PHP с проверкой существования поля...

Спасибо за подсказку!
Решил так:

$result = $db->query("SHOW COLUMNS FROM ".PREFIX."_post WHERE Field = 'region_id'");
if(!$result->num_rows) $db->query("ALTER TABLE ".PREFIX."_post ADD `region_id` VARCHAR(190) DEFAULT NULL");
$db->close();

 

[ravchik]

create or replace procedure addFieldIfNotExists(IN table_name_IN varchar(100), IN field_name_IN varchar(100),
                                                IN field_definition_IN varchar(100))
BEGIN

    SET @isFieldThere = isFieldExisting(table_name_IN, field_name_IN);
    IF (@isFieldThere = 0) THEN

        SET @ddl = CONCAT('ALTER TABLE ', table_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN');
        SET @ddl = CONCAT(@ddl, ' ', field_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', field_definition_IN);

        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END IF;

END;

create or replace function isFieldExisting(table_name_IN varchar(100), field_name_IN varchar(100)) returns int
    RETURN (
        SELECT COUNT(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.columns
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = table_name_IN
          AND COLUMN_NAME = field_name_IN
    );

вот процедура, не знаю - будет ли она работать на новых версиях мускула. Добавлял в некоторых релизах своей разработки дле апи.

пример использования.

CALL addFieldIfNotExists ('{prefix}_api_keys', 'own_only', 'boolean default false not null');

на постгре используется немного инной синтакс, чем в мускуле, поэтому проверяй сам - вызывается ли функционал или нет

Вариант имеет право на жизьн. Но, мне кажется, вариант с php-проверкой проще и удобнее. Но за участие - спасибо!

 

[MaHarder]

Но, мне кажется, вариант с php-проверкой проще и удобнее.

процедура - она же функция у баз данных - работает чисто на языке мускула, не нужны дополнительные скрипты и прочее. а в контексте с ДЛЕ эта функция мне понадобилась при обновлении. В принципе, код выше делает тоже самое, но его не вставить в поля для мускула

 

[ravchik]

процедура - она же функция у баз данных - работает чисто на языке мускула, не нужны дополнительные скрипты и прочее. а в контексте с ДЛЕ эта функция мне понадобилась при обновлении. В принципе, код выше делает тоже самое, но его не вставить в поля для мускула

Да, вы праы, в разных ситуация можно использовать разные решения. В конкретной ситуации решение на php мне кажется проще.

 

[ravchik]

Решение от dj-avtosh на сайте dle-faq.ru:

SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = "{prefix}_post")
      AND (table_schema = DATABASE())
      AND (column_name = "region_id")
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", "{prefix}_post", " ADD ", "region_id", " VARCHAR(190) DEFAULT NULL;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;