Rails 4.2.6. - Как создать миграцию с Хранимыми процедурами?

#mysql #ruby_on_rails #procedure

Всем привет!
Как создать хранимые процедуры в миграциях?
Спасибо.

Рабочий код:

DELIMITER |
DROP PROCEDURE IF EXISTS search_result |
CREATE PROCEDURE search_result(IN search_data VARCHAR(255))

BEGIN
  DROP TABLE IF EXISTS searchTableRes;
  CREATE TABLE IF NOT EXISTS searchTableRes (
          `id` INT NOT NULL,
          `title` VARCHAR(255),
          `article` TEXT,
          `articleable_id` INT,
          `articleable_type` VARCHAR(255)
  )ENGINE=MYISAM SET utf8 COLLATE utf8_general_ci;

  INSERT INTO searchTableRes (
    `id`,
    `title`,
    `article`,
    `articleable_id`,
    `articleable_type`
  )

  SELECT
        `a`.`id` as `id`,
        `a`.`title` as `title`,
        `a`.`article` as `article`,
        `a`.`articleable_id` as `articleable_id`,
        `a`.`articleable_type` as `articleable_type`
    FROM `articles` `a`;

  CREATE FULLTEXT INDEX ixFull ON searchTableRes (`title`, `article`);
  SELECT *,MATCH(`title`, `article`)
                 AGAINST (CONCAT('*',search_data,'*') IN NATURAL LANGUAGE MODE) AS
coefficient
            FROM searchTableRes
              WHERE MATCH(`title`, `article`)
                          AGAINST (CONCAT('*',search_data,'*') IN BOOLEAN MODE)
              ORDER BY coefficient DESC;
  ALTER table searchTableRes ENGINE = BLACKHOLE;
END
|
DELIMITER ;

    

Ответы

Ответ 1

Вы можете воспользоваться ActiveRecord::Base.connection.execute() для выполнения
чистого SQL-запроса. Можно поступить примерно так

class CreateProcedure < ActiveRecord::Migration
  def change
    sql = 'DROP PROCEDURE IF EXISTS search_result';
    ActiveRecord::Base.connection.execute sql
    sql << 'CREATE PROCEDURE search_result(IN search_data VARCHAR(255))

BEGIN
  DROP TABLE IF EXISTS searchTableRes;
  CREATE TABLE IF NOT EXISTS searchTableRes (
          `id` INT NOT NULL,
          `title` VARCHAR(255),
          `article` TEXT,
          `articleable_id` INT,
          `articleable_type` VARCHAR(255)
  )ENGINE=MYISAM SET utf8 COLLATE utf8_general_ci;

  INSERT INTO searchTableRes (
    `id`,
    `title`,
    `article`,
    `articleable_id`,
    `articleable_type`
  )

  SELECT
        `a`.`id` as `id`,
        `a`.`title` as `title`,
        `a`.`article` as `article`,
        `a`.`articleable_id` as `articleable_id`,
        `a`.`articleable_type` as `articleable_type`
    FROM `articles` `a`;

  CREATE FULLTEXT INDEX ixFull ON searchTableRes (`title`, `article`);
  SELECT *,MATCH(`title`, `article`)
                 AGAINST (CONCAT('*',search_data,'*') IN NATURAL LANGUAGE MODE) AS
coefficient
            FROM searchTableRes
              WHERE MATCH(`title`, `article`)
                          AGAINST (CONCAT('*',search_data,'*') IN BOOLEAN MODE)
              ORDER BY coefficient DESC;
  ALTER table searchTableRes ENGINE = BLACKHOLE;
END';
    ActiveRecord::Base.connection.execute sql
  end
end


Ответ 2

Ну, когда язык запросов и миграций ActiveRecord не может предложить необходимых удобств
БД из доступных, всегда есть execute у соединения, который позволяет выполнить произвольный SQL.

Например, можно сделать:

ActiveRecord::Base.connection.execute(запрос, пояснение_для_логов)


Хорошо сочетается с heredoc-синтаксисом строк:

запрос = <<-SQL
  SELECT * FROM table
SQL


Некоторые редакторы могут даже опознавать по маркеру окончания (здесь SQL) язык строки
и подсвечивать её соответствующим образом, что облегчает редактирование. Или, как вариант,
можно вынести SQL в отдельный *.sql-файл и редактировать его в режиме SQL. Это косметика,
но жить с ней немножко легче. 



Для использования продвинутых возможностей реляционных баз может иметь смысл заменить
ActiveRecord на Sequel.


Ответ 3

Учитывая ответы cheops и D-side, получился следующее:


  Это готовый пример реализации умного и быстрого поиска с помощью 'хранимых процедур',
что и запускается из миграции бес проблем. 


# /db/migrate/20160806225544_create_searches.rb:
class CreateSearches < ActiveRecord::Migration   
  def change
    ActiveRecord::Base.connection.execute('DROP PROCEDURE IF EXISTS search')
    ActiveRecord::Base.clear_active_connections!

    sql = <<-SQL 
CREATE PROCEDURE search(IN search_data VARCHAR(255)) 
BEGIN
DROP TABLE IF EXISTS searches;

CREATE TABLE IF NOT EXISTS searches (
    `article_id`            INT(11),
    `article_title`         VARCHAR(255),
    `article`               TEXT,
    `article_index`         VARCHAR(255),
    `articleable_id`        INT,
    `articleable_type`      VARCHAR(255),
    `article_position`      INT,
    `article_published`     INT,

    `texture_title`         VARCHAR(255),
    `texture_position`      INT,
    `texture_published`     INT,

    `message_index`         VARCHAR(255),
    `message_name`          VARCHAR(255),
    `message_contact`       VARCHAR(255),
    `message_product_id`    INT,
    `message_date`          DATETIME,
    `message_position`      INT,
    `message_published`     INT,

    `page_title`            VARCHAR(255),
    `page_slug`             VARCHAR(255),
    `page_menu_id`          INT,
    `page_parent_id`        INT,
    `page_action_template`  VARCHAR(255),
    `page_position`         INT,
    `page_published`        INT,

    `product_title`         VARCHAR(255),
    `product_price`         VARCHAR(255),
    `product_menu_id`       INT,
    `product_texture_id`    INT,
    `product_view_id`       INT,
    `product_filling_id`    INT,
    `product_seat_count_id` INT,
    `product_position`      INT(11),
    `product_published`     INT(11)   
)ENGINE=MYISAM DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
INSERT INTO searches (
    `article_id`           ,
    `article_title`        ,
    `article`              ,
    `article_index`        ,
    `articleable_id`       ,
    `articleable_type`     ,
    `article_position`     ,
    `article_published`    ,

    `texture_title`        ,
    `texture_position`     ,
    `texture_published`    ,

    `message_index`        ,
    `message_name`         ,
    `message_contact`      ,
    `message_product_id`   ,
    `message_date`         ,
    `message_position`     ,
    `message_published`    ,

    `page_title`           ,
    `page_slug`            ,
    `page_menu_id`         ,
    `page_parent_id`       ,
    `page_action_template` ,
    `page_position`        ,
    `page_published`       ,

    `product_title`        ,
    `product_price`        ,
    `product_menu_id`      ,
    `product_texture_id`   ,
    `product_view_id`      ,
    `product_filling_id`   ,
    `product_seat_count_id`,
    `product_position`     ,
    `product_published`   
)   
SELECT DISTINCT
    `a`.`id`                      as `article_id`           ,
    `a`.`title`                   as `article_title`        ,
    `a`.`article`                 as `article`              ,
    `a`.`index`                   as `article_index`        ,
    `a`.`articleable_id`          as `articleable_id`       ,
    `a`.`articleable_type`        as `articleable_type`     ,
    `a`.`position`                as `article_position`     ,
    `a`.`published`               as `article_published`    ,

    `t`.`title`                   as `texture_title`        ,
    `t`.`position`                as `texture_position`     ,
    `t`.`published`               as `texture_published`    ,

    `m`.`index`                   as `message_index`        ,
    `m`.`name`                    as `message_name`         ,
    `m`.`contact`                 as `message_contact`      ,
    `m`.`product_id`              as `message_product_id`   ,
    `m`.`date`                    as `message_date`         ,
    `m`.`position`                as `message_position`     ,
    `m`.`published`               as `message_published`    ,

    `p`.`title`                   as `page_title`           ,
    `p`.`slug`                    as `page_slug`            ,
    `p`.`menu_id`                 as `page_menu_id`         ,
    `p`.`parent_id`               as `page_parent_id`       ,
    `p`.`action_template`         as `page_action_template` ,
    `p`.`position`                as `page_position`        ,
    `p`.`published`               as `page_published`       ,

    `pc`.`title`                  as `product_title`        ,
    `pc`.`price`                  as `product_price`        ,
    `pc`.`menu_id`                as `product_menu_id`      ,
    `pc`.`texture_id`             as `product_texture_id`   ,
    `pc`.`product_view_id`        as `product_view_id`      ,
    `pc`.`product_filling_id`     as `product_filling_id`   ,
    `pc`.`product_seat_count_id`  as `product_seat_count_id`,
    `pc`.`position`               as `product_position`     ,
    `pc`.`published`              as `product_published`
FROM `articles` `a`   LEFT OUTER JOIN `textures` `t`
    ON `a`.`articleable_id` = `t`.`id`   LEFT OUTER JOIN `messages` `m`
    ON `a`.`articleable_id` = `m`.`id`   LEFT OUTER JOIN `pages` `p`
    ON `a`.`articleable_id` = `p`.`id`   LEFT OUTER JOIN `products` `pc`
    ON `a`.`articleable_id` = `pc`.`id`

UNION

SELECT  DISTINCT
    `a`.`id`                      as `article_id`           ,
    `a`.`title`                   as `article_title`        ,
    `a`.`article`                 as `article`              ,
    `a`.`index`                   as `article_index`        ,
    `a`.`articleable_id`          as `articleable_id`       ,
    `a`.`articleable_type`        as `articleable_type`     ,
    `a`.`position`                as `article_position`     ,
    `a`.`published`               as `article_published`    ,

    `t`.`title`                   as `texture_title`        ,
    `t`.`position`                as `texture_position`     ,
    `t`.`published`               as `texture_published`    ,

    `m`.`index`                   as `message_index`        ,
    `m`.`name`                    as `message_name`         ,
    `m`.`contact`                 as `message_contact`      ,
    `m`.`product_id`              as `message_product_id`   ,
    `m`.`date`                    as `message_date`         ,
    `m`.`position`                as `message_position`     ,
    `m`.`published`               as `message_published`    ,

    `p`.`title`                   as `page_title`           ,
    `p`.`slug`                    as `page_slug`            ,
    `p`.`menu_id`                 as `page_menu_id`         ,
    `p`.`parent_id`               as `page_parent_id`       ,
    `p`.`action_template`         as `page_action_template` ,
    `p`.`position`                as `page_position`        ,
    `p`.`published`               as `page_published`       ,

    `pc`.`title`                  as `product_title`        ,
    `pc`.`price`                  as `product_price`        ,
    `pc`.`menu_id`                as `product_menu_id`      ,
    `pc`.`texture_id`             as `product_texture_id`   ,
    `pc`.`product_view_id`        as `product_view_id`      ,
    `pc`.`product_filling_id`     as `product_filling_id`   ,
    `pc`.`product_seat_count_id`  as `product_seat_count_id`,
    `pc`.`position`               as `product_position`     ,
    `pc`.`published`              as `product_published`
FROM `articles` `a`
RIGHT OUTER JOIN `textures` `t`
    ON `a`.`articleable_id` = `t`.`id`   RIGHT OUTER JOIN `messages` `m`
    ON `a`.`articleable_id` = `m`.`id`   RIGHT OUTER JOIN `pages` `p`
    ON `a`.`articleable_id` = `p`.`id`   RIGHT OUTER JOIN `products` `pc`
    ON `a`.`articleable_id` = `pc`.`id`;

CREATE FULLTEXT INDEX ixFull
    ON searches (
      `article_title`,
      `article`      ,
      `texture_title`,
      `page_title`   ,
      `product_title`
);

SELECT
    *,MATCH(
      `article_title`,
      `article`      ,
      `texture_title`,
      `page_title`   ,
      `product_title`
    )
AGAINST (CONCAT('*',search_data,'*') IN NATURAL LANGUAGE MODE) AS coefficient
FROM searches
WHERE MATCH(
      `article_title`,
      `article`      ,
      `texture_title`,
      `page_title`   ,
      `product_title`
    )
AGAINST (CONCAT('*',search_data,'*') IN BOOLEAN MODE)
ORDER BY coefficient DESC;

ALTER table searches ENGINE = BLACKHOLE; 
END 
SQL

    ActiveRecord::Base.connection.execute(sql)
    ActiveRecord::Base.clear_active_connections!
   end 
end

# /app/controllers/searches_controller.rb:    
class SearchesController < InheritedResources::Base
  def show
    @result = ActiveRecord::Base.connection.execute("call search('#{params[:search]}')")
    ActiveRecord::Base.clear_active_connections!
  end
end