Hi all, For those of you working wih version control (especially svn), how do you track database changes during development? Right now, every now and then I manually generate a schema dump and check it in with the rest of the source. But I''m not doing it for every code change that originates from or implicates in a change in the schema. I guess I could write a script to generate a new dump automatically on every commit. Ideally, I''d like to log every database sql command issued to change the database, and when updating a working copy on another machine, execute the sql diff. Anyway, before I go scripting around, I''d like to get the opnions of other developers on how to handle this.
Caio Chassot wrote:> Hi all, > > For those of you working wih version control (especially svn), how do > you track database changes during development?I start with a schema.sql file and always update the database by editing that file, then recreating the database. I created a Rake target that recreates the database and reloads all of the reference data and development/test data. Jim -- Jim Menard, jimm-Xhj3G7Rj6JI@public.gmane.org, http://www.io.com/~jimm "I have a red sign on my door. It says ''If this sign is blue, you''re moving too fast.''" -- pyros on slashdot
On 4/25/05, Jim Menard <jimm-Xhj3G7Rj6JI@public.gmane.org> wrote:> I start with a schema.sql file and always update the database by editing that > file, then recreating the database. I created a Rake target that recreates the > database and reloads all of the reference data and development/test data.This is how I work as well, although it becomes a pain when you start to maintain multiple SQL scripts (i.e. schema-sqlite.sql and schema-mysql.sql). What would be nice would be if you could represent the database schema using XML then do an XSLT depending on which db you are targeting.
Jim Menard wrote:> Caio Chassot wrote: >> For those of you working wih version control (especially svn), how do >> you track database changes during development? > > I start with a schema.sql file and always update the database by editing > that file, then recreating the database. I created a Rake target that > recreates the database and reloads all of the reference data and > development/test data.I take this approach as well. ''rake regen'' rebuilds the the development and test databases from a master schema and populates them with my test fixtures. My unit tests each run within a transaction that''s rolled back in teardown, so the fixtures never need to be reloaded. Woohoo! jeremy
On 4/25/05, Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> wrote:> I take this approach as well. ''rake regen'' rebuilds the the development > and test databases from a master schema and populates them with my test > fixtures.So is Rake the right tool for this or would it be more Rails like to create a Generator which handles the creation of a new database and/or database-table? josh
Josh Knowles wrote:>This is how I work as well, although it becomes a pain when you start >to maintain multiple SQL scripts (i.e. schema-sqlite.sql and >schema-mysql.sql). >In RForum we defined the schema as ERB templates. create_db script reads the database connection particulars from config/database.yml and generates SQLs out of those templates according to the database type. -- Best regards, Alexey Verkhovsky Ruby Forum: http://ruby-forum.org (moderator) RForum: http://rforum.andreas-s.net (co-author) Instiki: http://instiki.org (maintainer)
> I start with a schema.sql file and always update the database by editing > that file, then recreating the database. I created a Rake target that > recreates the database and reloads all of the reference data and > development/test data.I tried the sql file approach, but I just get bored and update the db via cocoamysql :) And then there''s another issue: after you go into production, if you are going to update the production version with a new version that requires database changes, you''ll need to run schema and data transformation queries on existing data. You can''t just drop and create everything. Using a rake task interests me, but I''m not really all that familiar with rake. Any pointers?
Caio Chassot wrote:> And then there''s another issue: after you go into production, if you are > going to update the production version with a new version that requires > database changes, you''ll need to run schema and data transformation > queries on existing data. You can''t just drop and create everything.Good point.> Using a rake task interests me, but I''m not really all that familiar > with rake. Any pointers?http://rake.rubyforge.org/ Look for the "format of a Rakefile" link in the "Roadmap" section. Jim -- Jim Menard, jimm-Xhj3G7Rj6JI@public.gmane.org, http://www.io.com/~jimm "I have a red sign on my door. It says ''If this sign is blue, you''re moving too fast.''" -- pyros on slashdot
Caio Chassot wrote:>> I start with a schema.sql file and always update the database by >> editing that file, then recreating the database. I created a Rake >> target that recreates the database and reloads all of the reference >> data and development/test data. > > And then there''s another issue: after you go into production, if you are > going to update the production version with a new version that requires > database changes, you''ll need to run schema and data transformation > queries on existing data. You can''t just drop and create everything.David''s Migrations prototype tackles the problem of schema changes in an agile dev. environment. Schema changes are represented as Migration subclasses with up and down methods. You make a new migration for each release to your production server; the Migrator applies your migration to the production database on the fly and keeps a history of migrations you''ve applied so you can easily roll back. The prototype is limited to MySQL, awaiting a champion for the other databases. http://dev.rubyonrails.com/file/trunk/activerecord/lib/active_record/migration.rb http://dev.rubyonrails.com/file/trunk/activerecord/test/migration_mysql.rb http://dev.rubyonrails.com/file/trunk/activerecord/test/fixtures/migrations/1_people_have_last_names.rb http://dev.rubyonrails.com/file/trunk/activerecord/test/fixtures/migrations/2_we_need_reminders.rb Another approach is to use tools like mysqldiff. Enjoy, jeremy
On 26/04/2005, at 3:12 AM, Caio Chassot wrote:> Hi all, > > For those of you working wih version control (especially svn), how do > you track database changes during development?We use the db_structure_dump rake task, along with a few of our own: dump_dev_db, purge_dev_db, recreate_dev_db, populate_dev_db and refresh_dev_db. They populate the db with the fixture data and the SQL defined in /db/development_structure.sql Workflow for commit: $ Rake dump_dev_db $ svn commit and for updates: $ svn update $ Rake refresh_dev_db If you''re interested in the code I can email it to you off-list (I should really start a blog and just blog this stuff!) - tim lucas
Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> writes:> David''s Migrations prototype tackles the problem of schema changes in an > agile dev. environment. > > Schema changes are represented as Migration subclasses with up and down > methods. You make a new migration for each release to your production > server; the Migrator applies your migration to the production database > on the fly and keeps a history of migrations you''ve applied so you can > easily roll back. > > The prototype is limited to MySQL, awaiting a champion for the other > databases. > > http://dev.rubyonrails.com/file/trunk/activerecord/lib/active_record/migration.rb > http://dev.rubyonrails.com/file/trunk/activerecord/test/migration_mysql.rb > http://dev.rubyonrails.com/file/trunk/activerecord/test/fixtures/migrations/1_people_have_last_names.rb > http://dev.rubyonrails.com/file/trunk/activerecord/test/fixtures/migrations/2_we_need_reminders.rbThis is cool looking. I''m anxious to see how this fleshes out. -- doug-jGAhs73c5XxeoWH0uzbU5w@public.gmane.org
Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> writes:>> I start with a schema.sql file and always update the database by editing >> that file, then recreating the database. I created a Rake target that >> recreates the database and reloads all of the reference data and >> development/test data. > > > I take this approach as well. ''rake regen'' rebuilds the the development > and test databases from a master schema and populates them with my test > fixtures. My unit tests each run within a transaction that''s rolled > back in teardown, so the fixtures never need to be reloaded. Woohoo!Can someone post the rake targets that do this kind of stuff? Maybe the wiki would be a good place to start? Rake targets to load fixtures into dev database is probably something that should be in the default rails rakefile. -- doug-jGAhs73c5XxeoWH0uzbU5w@public.gmane.org
On Apr 25, 2005, at 10:18 PM, Doug Alcorn wrote:> Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> writes: > >>> I start with a schema.sql file and always update the database by >>> editing >>> that file, then recreating the database. I created a Rake target that >>> recreates the database and reloads all of the reference data and >>> development/test data. >> >> >> I take this approach as well. ''rake regen'' rebuilds the the >> development >> and test databases from a master schema and populates them with my >> test >> fixtures. My unit tests each run within a transaction that''s rolled >> back in teardown, so the fixtures never need to be reloaded. Woohoo! > > Can someone post the rake targets that do this kind of stuff? Maybe > the wiki would be a good place to start? Rake targets to load > fixtures into dev database is probably something that should be in the > default rails rakefile.In my case, the Rake target calls a script that does a few things: generates a small data file and a SQL file or two, tells PostgreSQL to load a bunch of data files, then run the SQL to do things like update primary key sequence values. I''m not sure you could make that kind of thing generic. Sure, you could have it load all the data files in a directory, but in many cases the data files must be loaded in a particular order, to satisfy foreign key constraints. Jim -- Jim Menard, jimm-Xhj3G7Rj6JI@public.gmane.org, http://www.io.com/~jimm/ "Quotation confesses inferiority." -- Ralph Waldo Emerson
Doug Alcorn wrote:> Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> writes: > >>I take this approach as well. ''rake regen'' rebuilds the the development >>and test databases from a master schema and populates them with my test >>fixtures. My unit tests each run within a transaction that''s rolled >>back in teardown, so the fixtures never need to be reloaded. Woohoo! > > Can someone post the rake targets that do this kind of stuff? Maybe > the wiki would be a good place to start? Rake targets to load > fixtures into dev database is probably something that should be in the > default rails rakefile.Here''s a ''rake regen'' snippet: http://article.gmane.org/gmane.comp.lang.ruby.rails/3737 (PostgreSQL alert.) jeremy