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