Страницы

Поиск по вопросам

воскресенье, 21 октября 2018 г.

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

Всем привет! Как создать хранимые процедуры в миграциях? Спасибо.
Рабочий код:
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 ;


Ответ

Вы можете воспользоваться 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

Комментариев нет:

Отправить комментарий