I have to create a Rails migration which creates many triggers and stored procedures.
Normally one would do that using the execute
method, but because of the size of the statements, I'd rather keep them in an external file and reference it from the migration.
How can I do that? Is it even possible?
When you run db:migrate, rails will check a special table in the database which contains the timestamp of the last migration applied to the database. It will then apply all of the migrations with timestamps after that date and update the database table with the timestamp of the last migration.
Go to db/migrate subdirectory of your application and edit each file one by one using any simple text editor. The ID column will be created automatically, so don't do it here as well. The method self. up is used when migrating to a new version, self.
Migrations are stored as files in the db/migrate directory, one for each migration class. The name of the file is of the form YYYYMMDDHHMMSS_create_products. rb, that is to say a UTC timestamp identifying the migration followed by an underscore followed by the name of the migration.
You can just store them in a text file and read them in via a File object.
sql = ""
source = File.new("./sql/procedures.sql", "r")
while (line = source.gets)
sql << line
end
source.close
execute sql
It's ugly, but works. I'd strongly recommend keeping the stored procedures/triggers inside migrations for easy rollbacks.
If you do the "external file" method, you'll need to maintain two additional files per migration, one for adding all the stuff, and one for dropping in in case of a:
rake db:rollback
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With