Hello! How to create stored procedures in migrations? Thank.

Work code:

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 ;
  • Well ... Naked SQL, obviously. - D-side
  • @D-side, the questioner is never “obvious.” - Sasha Chernykh
  • @ SashaBlack it depends on the questioner. He is clever enough to compile the migration with SQL, but he did not realize that it can be started in the same form. It could be that “last step” before guessing. - D-side

3 answers 3

You can use ActiveRecord::Base.connection.execute() to execute a pure SQL query. You can do something like this

 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
  • 6 seconds, Karl! : D - D-side
  • : D ____________ - cheops

Well, when the query and migration language ActiveRecord cannot offer the necessary database conveniences available, there is always an execute connection that allows you to execute arbitrary SQL.

For example, you can do:

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

It goes well with the heredoc syntax of strings:

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

Some editors may even recognize the end language (here SQL ) of the string language and highlight it accordingly, which makes editing easier. Or, alternatively, you can put the SQL into a separate *.sql file and edit it in SQL mode. This is cosmetics, but living with it is a little bit easier.

To use the advanced features of relational databases, it may make sense to replace ActiveRecord with Sequel .

    Given the answers cheops and D-side , the following has turned out:

    This is a ready-made example of implementing a smart and fast search using 'stored procedures', which is what starts from migration without any problems.

     # /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