I have an issue with stored procedures and the test database in Rails 3.0.7. When running
我对Rails 3.0.7中的存储过程和测试数据库有问题。运行时
rake db:test:prepare
rake db:测试准备
it migrates the db tables from schema.rb and not from migrations directly. The procedures are created within migrations by calling the execute method and passing in an SQL string such as CREATE FUNCTION foo() ... BEGIN ... END;.
它从模式迁移db表。rb而不是直接从迁移。通过调用execute方法并传入SQL字符串(如CREATE FUNCTION foo())，在迁移过程中创建过程。开始……,结束。
So after researching, I found that you should use
所以在研究之后，我发现你应该使用
config.active_record.schema_format = :sql
config.active_record。schema_format =:sql
inside application.rb. After adding this line, I executed
application.rb内部。添加这一行之后，我执行了。
rake db:structure:dump rake db:test:clone_structure
rake db:结构:转储rake db:测试:clone_structure
The first one is supposed to dump the structure into a development.sql file and the second one creates the testing database from this file. But my stored procedures, and functions are still not appearing in the testing db. If anyone knows something about this issue. Help will be appreciated.
第一个应该将结构转储到开发中。sql文件和第二个文件从这个文件创建测试数据库。但是我的存储过程和函数仍然没有出现在测试db中。如果有人知道这个问题的话。帮助将不胜感激。
I also tried running rake db:test:prepare again, but still no results.
我还尝试了运行rake db:test:prepare，但是仍然没有结果。
MySQL 5.5, Rails 3.0.7, Ruby 1.8.7.
MySQL 5.5, Rails 3.0.7, Ruby 1.8.7。
Thanks in advance!
提前谢谢!
7 个解决方案
#1
9
There is no other rake task for that and structure_dump is defined like this:
没有其他rake任务，structure_dump定义如下:
# File activerecord/lib/active_record/connection_adapters/mysql_adapter.rb, line 354
def structure_dump #:nodoc:
if supports_views?
sql = "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'"
else
sql = "SHOW TABLES"
end
select_all(sql).map do |table|
table.delete('Table_type')
select_one("SHOW CREATE TABLE #{quote_table_name(table.to_a.first.last)}")["Create Table"] + ";\n\n"
end.join("")
end
so it apparently works only for tables, not procedures, unless you monkeypatch it.
所以它显然只适用于表格，而不是程序，除非你把它蒙上。
The only solution as far as I know is to use shell:
据我所知，唯一的解决方案是使用shell:
mysqldump -R -u <user> <development_database> | mysql -u <user> <test_database>
#2
1
DISCLAIMER : I am not a Ruby-on-Rails Programmer
免责声明:我不是一个在rails上运行的程序员
Strictly in terms of MySQL, you basically have two ways to extract Stored Procedures (SP) and Stored Functions (SF).
从MySQL的角度来看，您基本上有两种方法来提取存储过程(SP)和存储函数(SF)。
Keep in mind that mysql.proc and information_schema.routines provide the housing on disk and in memory for SPs. Yet, there are 2 SQL statements to retrieve them: SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION.
记住，mysql。proc和information_schema。例程为SPs提供磁盘和内存中的外壳。但是，有两个SQL语句可以检索它们:显示创建过程和显示创建函数。
The first way involves collecting all SPs and SFs using mysql.proc and form them into SQL statements that expose them.
第一种方法是使用mysql收集所有的SPs和SFs。proc并将它们形成SQL语句来公开它们。
Example I have 6 SPs and 2 SFs in my test database. Here is how to generate SQL for all 8 routines:
例如，我的测试数据库中有6个SPs和2个SFs。下面是如何为所有8例例程生成SQL:
mysql> SELECT CONCAT('SHOW CREATE ',type,' `',db,'`.`',name,'`\\G') SQLStatements FROM mysql.proc;
+-----------------------------------------------------+
| SQLStatements |
+-----------------------------------------------------+
| SHOW CREATE PROCEDURE `test`.`CreateSampleTable`\G |
| SHOW CREATE PROCEDURE `test`.`CreateSampleTables`\G |
| SHOW CREATE PROCEDURE `test`.`GetMissingIntegers`\G |
| SHOW CREATE FUNCTION `test`.`GetTestTableCounts`\G |
| SHOW CREATE PROCEDURE `test`.`ImportWeeklyBatch`\G |
| SHOW CREATE FUNCTION `test`.`InsertName`\G |
| SHOW CREATE PROCEDURE `test`.`LoadSampleTables`\G |
| SHOW CREATE PROCEDURE `test`.`MigrateColumn`\G |
+-----------------------------------------------------+
8 rows in set (0.00 sec)
You can cycle through and collect the code needed to each stored procedure and function.
您可以循环并收集每个存储过程和函数所需的代码。
Triggers must be collected separately.
必须分别收集触发器。
In MySQL 5.x you can collect triggers using this query:
在MySQL中5。您可以使用以下查询收集触发器:
mysql> SELECT CONCAT('SHOW CREATE TRIGGER `',trigger_schema,'`.`',trigger_name,'`\\G') SQLStatements FROM information_schema.triggers;
+--------------------------------------------------+
| SQLStatements |
+--------------------------------------------------+
| SHOW CREATE TRIGGER `test`.`AddPermTempKey`\G |
| SHOW CREATE TRIGGER `test`.`DeletePermTempKey`\G |
+--------------------------------------------------+
or to save time UNION the two SQL statements
或者为了节省时间，将这两个SQL语句联合起来
mysql> SELECT CONCAT('SHOW CREATE ',type,' `',db,'`.`',name,'`\\G') SQLStatements FROM mysql.proc UNION SELECT CONCAT('SHOW CREATE TRIGGER `',trigger_schema,'`.`',trigger_name,'`\\G') SQLStatements FROM information_schema.triggers;
+-----------------------------------------------------+
| SQLStatements |
+-----------------------------------------------------+
| SHOW CREATE PROCEDURE `test`.`CreateSampleTable`\G |
| SHOW CREATE PROCEDURE `test`.`CreateSampleTables`\G |
| SHOW CREATE PROCEDURE `test`.`GetMissingIntegers`\G |
| SHOW CREATE FUNCTION `test`.`GetTestTableCounts`\G |
| SHOW CREATE PROCEDURE `test`.`ImportWeeklyBatch`\G |
| SHOW CREATE FUNCTION `test`.`InsertName`\G |
| SHOW CREATE PROCEDURE `test`.`LoadSampleTables`\G |
| SHOW CREATE PROCEDURE `test`.`MigrateColumn`\G |
| SHOW CREATE TRIGGER `test`.`AddPermTempKey`\G |
| SHOW CREATE TRIGGER `test`.`DeletePermTempKey`\G |
+-----------------------------------------------------+
10 rows in set (0.07 sec)
The second way is the preferred way for DBAs, using mysqldump.
第二种方法是使用mysqldump的dba的首选方法。
This will collect all table structures, SPs, SFs, and triggers in a single file.
这将在一个文件中收集所有表结构、SPs、SFs和触发器。
mysqldump -h... -u... -p... --no-data --routines --triggers --all-databases > MySQLSchema.sql
This will do the same but without the CREATE TABLE stuff:
这将做同样的事情，但没有创建表格的东西:
mysqldump -h... -u... -p... --no-data --no-create-info --routines --triggers --all-databases > MySQLSchema.sql
Give these a Try !!!
试一试吧!!!
#3
0
Was searching for how to do the same thing then saw this: http://guides.rubyonrails.org/migrations.html#types-of-schema-dumps
搜索如何做同样的事情，然后看到了:http://guides.rubyonrails.org/migrations.html# schema-dumps
To quote:
引用:
"db/schema.rb cannot express database specific items such as foreign key constraints, triggers or stored procedures. While in a migration you can execute custom SQL statements, the schema dumper cannot reconstitute those statements from the database. If you are using features like this then you should set the schema format to :sql."
“db /模式。rb不能表示数据库特定的项，如外键约束、触发器或存储过程。在迁移过程中，您可以执行定制的SQL语句，但是模式dumper不能从数据库中重新构建这些语句。如果您正在使用这样的特性，那么您应该将模式格式设置为:sql。
i.e.:
例如:
config.active_record.schema_format = :sql
config.active_record。schema_format =:sql
I haven't tried it yet myself though so I'll post a follow-up later.
我自己还没有尝试过，所以我稍后会发布后续的消息。
#4
0
I took Matthew Bass's method of removing existing rake task and redefined a task using mysqldump with the options that RolandoMySQLDBA provided
我采用了Matthew Bass删除现有rake任务的方法，并使用RolandoMySQLDBA提供的选项重新定义了一个任务
http://matthewbass.com/2007/03/07/overriding-existing-rake-tasks/
http://matthewbass.com/2007/03/07/overriding-existing-rake-tasks/
Rake::TaskManager.class_eval do
def remove_task(task_name)
@tasks.delete(task_name.to_s)
end
end
def remove_task(task_name)
Rake.application.remove_task(task_name)
end
# Override existing test task to prevent integrations
# from being run unless specifically asked for
remove_task 'db:test:prepare'
namespace :db do
namespace :test do
desc "Create a db/schema.rb file"
task :prepare => :environment do
sh "mysqldump --routines --no-data -u root ni | mysql -u root ni_test"
end
end
end
#5
0
If you want Ruby dumps (as opposed to SQL dumps) you might give this gem a try:
如果您想要Ruby转储(而不是SQL转储)，您可以尝试一下这个gem:
https://github.com/jovoto-team/trackless_triggers
https://github.com/jovoto-team/trackless_triggers
It supports dumping triggers and functions for mysql out of the box without introducing new rake tasks. It is based on tenderlove's trigger_happy plugin.
它支持直接为mysql倾倒触发器和函数，而不引入新的rake任务。它基于tenderlove的trigger_happy插件。
#6
0
Looks like (I have not tested) rake db:structure:dump support for stored functions and procedures has been added in Rails 5. See this commit in the rails GitHub project. The --routines flag to mysqldump is described here. Note method structure_dump looks very different than when Jan Minárik answered six years ago.
看起来(我还没有测试)rake db:structure:在Rails 5中添加了对存储函数和过程的转储支持。请参见rails GitHub项目中的提交。这里描述了到mysqldump的例程标志。注意，structure_dump方法看起来与Jan Minarik六年前回答时非常不同。
#7
0
On Rails 4 I hooked it in as a post load hook on the db:test:load rake task as follows:
在Rails 4上，我将它作为一个post - load hook连接到db:test:load rake任务如下:
require File.expand_path('../config/application', __FILE__)
Rails.application.load_tasks
namespace :db do
namespace :test do
task :post_load_hook do
re_create_sps
end
def re_create_sps
[20170905123456, 20170905123457].each do |version|
ActiveRecord::Migrator.run(
:down, ActiveRecord::Migrator.migrations_paths, version)
ActiveRecord::Migrator.run(
:up, ActiveRecord::Migrator.migrations_paths, version)
end
end
# stored procs must be restored each time.
end
end
Rake::Task['db:test:load'].enhance(['db:test:post_load_hook'])
This approach will run automatically so you don't have to manually reload the db each test run and it will only affect the db:test:load task, so I think it is fairly isolated.
这种方法将自动运行，因此您不必在每次测试运行时手动重新加载db，它只会影响db:test:load任务，因此我认为它是相当独立的。
IMHO a bit ugly having the migration ids in the task, so you could alternatively extract the migration code to a lib and call it from both the migration and the Rake task above to clean it up.
在任务中包含迁移id有点难看，因此您可以选择将迁移代码提取到库中，并从上面的迁移和Rake任务中调用它来清理它。
#1
9
There is no other rake task for that and structure_dump is defined like this:
没有其他rake任务，structure_dump定义如下:
# File activerecord/lib/active_record/connection_adapters/mysql_adapter.rb, line 354
def structure_dump #:nodoc:
if supports_views?
sql = "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'"
else
sql = "SHOW TABLES"
end
select_all(sql).map do |table|
table.delete('Table_type')
select_one("SHOW CREATE TABLE #{quote_table_name(table.to_a.first.last)}")["Create Table"] + ";\n\n"
end.join("")
end
so it apparently works only for tables, not procedures, unless you monkeypatch it.
所以它显然只适用于表格，而不是程序，除非你把它蒙上。
The only solution as far as I know is to use shell:
据我所知，唯一的解决方案是使用shell:
mysqldump -R -u <user> <development_database> | mysql -u <user> <test_database>
#2
1
DISCLAIMER : I am not a Ruby-on-Rails Programmer
免责声明:我不是一个在rails上运行的程序员
Strictly in terms of MySQL, you basically have two ways to extract Stored Procedures (SP) and Stored Functions (SF).
从MySQL的角度来看，您基本上有两种方法来提取存储过程(SP)和存储函数(SF)。
Keep in mind that mysql.proc and information_schema.routines provide the housing on disk and in memory for SPs. Yet, there are 2 SQL statements to retrieve them: SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION.
记住，mysql。proc和information_schema。例程为SPs提供磁盘和内存中的外壳。但是，有两个SQL语句可以检索它们:显示创建过程和显示创建函数。
The first way involves collecting all SPs and SFs using mysql.proc and form them into SQL statements that expose them.
第一种方法是使用mysql收集所有的SPs和SFs。proc并将它们形成SQL语句来公开它们。
Example I have 6 SPs and 2 SFs in my test database. Here is how to generate SQL for all 8 routines:
例如，我的测试数据库中有6个SPs和2个SFs。下面是如何为所有8例例程生成SQL:
mysql> SELECT CONCAT('SHOW CREATE ',type,' `',db,'`.`',name,'`\\G') SQLStatements FROM mysql.proc;
+-----------------------------------------------------+
| SQLStatements |
+-----------------------------------------------------+
| SHOW CREATE PROCEDURE `test`.`CreateSampleTable`\G |
| SHOW CREATE PROCEDURE `test`.`CreateSampleTables`\G |
| SHOW CREATE PROCEDURE `test`.`GetMissingIntegers`\G |
| SHOW CREATE FUNCTION `test`.`GetTestTableCounts`\G |
| SHOW CREATE PROCEDURE `test`.`ImportWeeklyBatch`\G |
| SHOW CREATE FUNCTION `test`.`InsertName`\G |
| SHOW CREATE PROCEDURE `test`.`LoadSampleTables`\G |
| SHOW CREATE PROCEDURE `test`.`MigrateColumn`\G |
+-----------------------------------------------------+
8 rows in set (0.00 sec)
You can cycle through and collect the code needed to each stored procedure and function.
您可以循环并收集每个存储过程和函数所需的代码。
Triggers must be collected separately.
必须分别收集触发器。
In MySQL 5.x you can collect triggers using this query:
在MySQL中5。您可以使用以下查询收集触发器:
mysql> SELECT CONCAT('SHOW CREATE TRIGGER `',trigger_schema,'`.`',trigger_name,'`\\G') SQLStatements FROM information_schema.triggers;
+--------------------------------------------------+
| SQLStatements |
+--------------------------------------------------+
| SHOW CREATE TRIGGER `test`.`AddPermTempKey`\G |
| SHOW CREATE TRIGGER `test`.`DeletePermTempKey`\G |
+--------------------------------------------------+
or to save time UNION the two SQL statements
或者为了节省时间，将这两个SQL语句联合起来
mysql> SELECT CONCAT('SHOW CREATE ',type,' `',db,'`.`',name,'`\\G') SQLStatements FROM mysql.proc UNION SELECT CONCAT('SHOW CREATE TRIGGER `',trigger_schema,'`.`',trigger_name,'`\\G') SQLStatements FROM information_schema.triggers;
+-----------------------------------------------------+
| SQLStatements |
+-----------------------------------------------------+
| SHOW CREATE PROCEDURE `test`.`CreateSampleTable`\G |
| SHOW CREATE PROCEDURE `test`.`CreateSampleTables`\G |
| SHOW CREATE PROCEDURE `test`.`GetMissingIntegers`\G |
| SHOW CREATE FUNCTION `test`.`GetTestTableCounts`\G |
| SHOW CREATE PROCEDURE `test`.`ImportWeeklyBatch`\G |
| SHOW CREATE FUNCTION `test`.`InsertName`\G |
| SHOW CREATE PROCEDURE `test`.`LoadSampleTables`\G |
| SHOW CREATE PROCEDURE `test`.`MigrateColumn`\G |
| SHOW CREATE TRIGGER `test`.`AddPermTempKey`\G |
| SHOW CREATE TRIGGER `test`.`DeletePermTempKey`\G |
+-----------------------------------------------------+
10 rows in set (0.07 sec)
The second way is the preferred way for DBAs, using mysqldump.
第二种方法是使用mysqldump的dba的首选方法。
This will collect all table structures, SPs, SFs, and triggers in a single file.
这将在一个文件中收集所有表结构、SPs、SFs和触发器。
mysqldump -h... -u... -p... --no-data --routines --triggers --all-databases > MySQLSchema.sql
This will do the same but without the CREATE TABLE stuff:
这将做同样的事情，但没有创建表格的东西:
mysqldump -h... -u... -p... --no-data --no-create-info --routines --triggers --all-databases > MySQLSchema.sql
Give these a Try !!!
试一试吧!!!
#3
0
Was searching for how to do the same thing then saw this: http://guides.rubyonrails.org/migrations.html#types-of-schema-dumps
搜索如何做同样的事情，然后看到了:http://guides.rubyonrails.org/migrations.html# schema-dumps
To quote:
引用:
"db/schema.rb cannot express database specific items such as foreign key constraints, triggers or stored procedures. While in a migration you can execute custom SQL statements, the schema dumper cannot reconstitute those statements from the database. If you are using features like this then you should set the schema format to :sql."
“db /模式。rb不能表示数据库特定的项，如外键约束、触发器或存储过程。在迁移过程中，您可以执行定制的SQL语句，但是模式dumper不能从数据库中重新构建这些语句。如果您正在使用这样的特性，那么您应该将模式格式设置为:sql。
i.e.:
例如:
config.active_record.schema_format = :sql
config.active_record。schema_format =:sql
I haven't tried it yet myself though so I'll post a follow-up later.
我自己还没有尝试过，所以我稍后会发布后续的消息。
#4
0
I took Matthew Bass's method of removing existing rake task and redefined a task using mysqldump with the options that RolandoMySQLDBA provided
我采用了Matthew Bass删除现有rake任务的方法，并使用RolandoMySQLDBA提供的选项重新定义了一个任务
http://matthewbass.com/2007/03/07/overriding-existing-rake-tasks/
http://matthewbass.com/2007/03/07/overriding-existing-rake-tasks/
Rake::TaskManager.class_eval do
def remove_task(task_name)
@tasks.delete(task_name.to_s)
end
end
def remove_task(task_name)
Rake.application.remove_task(task_name)
end
# Override existing test task to prevent integrations
# from being run unless specifically asked for
remove_task 'db:test:prepare'
namespace :db do
namespace :test do
desc "Create a db/schema.rb file"
task :prepare => :environment do
sh "mysqldump --routines --no-data -u root ni | mysql -u root ni_test"
end
end
end
#5
0
If you want Ruby dumps (as opposed to SQL dumps) you might give this gem a try:
如果您想要Ruby转储(而不是SQL转储)，您可以尝试一下这个gem:
https://github.com/jovoto-team/trackless_triggers
https://github.com/jovoto-team/trackless_triggers
It supports dumping triggers and functions for mysql out of the box without introducing new rake tasks. It is based on tenderlove's trigger_happy plugin.
它支持直接为mysql倾倒触发器和函数，而不引入新的rake任务。它基于tenderlove的trigger_happy插件。
#6
0
Looks like (I have not tested) rake db:structure:dump support for stored functions and procedures has been added in Rails 5. See this commit in the rails GitHub project. The --routines flag to mysqldump is described here. Note method structure_dump looks very different than when Jan Minárik answered six years ago.
看起来(我还没有测试)rake db:structure:在Rails 5中添加了对存储函数和过程的转储支持。请参见rails GitHub项目中的提交。这里描述了到mysqldump的例程标志。注意，structure_dump方法看起来与Jan Minarik六年前回答时非常不同。
#7
0
On Rails 4 I hooked it in as a post load hook on the db:test:load rake task as follows:
在Rails 4上，我将它作为一个post - load hook连接到db:test:load rake任务如下:
require File.expand_path('../config/application', __FILE__)
Rails.application.load_tasks
namespace :db do
namespace :test do
task :post_load_hook do
re_create_sps
end
def re_create_sps
[20170905123456, 20170905123457].each do |version|
ActiveRecord::Migrator.run(
:down, ActiveRecord::Migrator.migrations_paths, version)
ActiveRecord::Migrator.run(
:up, ActiveRecord::Migrator.migrations_paths, version)
end
end
# stored procs must be restored each time.
end
end
Rake::Task['db:test:load'].enhance(['db:test:post_load_hook'])
This approach will run automatically so you don't have to manually reload the db each test run and it will only affect the db:test:load task, so I think it is fairly isolated.
这种方法将自动运行，因此您不必在每次测试运行时手动重新加载db，它只会影响db:test:load任务，因此我认为它是相当独立的。
IMHO a bit ugly having the migration ids in the task, so you could alternatively extract the migration code to a lib and call it from both the migration and the Rake task above to clean it up.
在任务中包含迁移id有点难看，因此您可以选择将迁移代码提取到库中，并从上面的迁移和Rake任务中调用它来清理它。