Hi, So, I''m being a good little pragmatic programmer intern. I''ve got stuff under version control, I''ve got (almost) push-button releases, I''ve got unit and functional unit tests. The thing that''s holding back my perfect one-command release of my websites is upgrading the database. If the production website already has some data in it, I don''t want to erase it, obviously. Generally, I''m just adding fields to tables, or adding an additional field. Sometimes though, I have to move data around from column to column or table to table. What''s a good way to handle this pragmatically? Write a database-migration script for every release? Ew. That doesn''t handle going back in time, say if I bork a release. Other question: Say I have 10 websites up and running. What tools are out there that will let me monitor them, maybe run some functional tests on them, and send me a page/email when something blows up? Thanks, Jeo
That''s a really interesting issue you''ve raised there Jeo. I''ve noticed that the approach for handling your database schema which is used in the beta rails book (of keeping an up to date schema in a file called create.sql) is all very well for development before an initial release, but it''s hardly _agile_ as you can''t use it to upgrade. I''m working on a rails port of a PHP application, and although I haven''t got as far as the upgrade system yet, for the original I had a text file to which I appended changes to the database (whether they were schema changes, or modifications to the format of live data). A version number in the code, and one in the database, enabled me to see which lines of SQL needed to be executed to update the site to the version in the newly uploaded code. I got into a good habit of expressing all my schema changes as alterations to the existing database, rather than changing the base schema definition. I''m nearly up to this in rails, and I''d be quite happy to produce an ''upgrade system'' as a small re-usable component (perhaps a generator). My previous version of this did include putting up an "upgrade in progress" page on the live site while the rsync was in progress. However, I hadn''t considered making the progress undo-able. How would that work? Finally, I don''t currently have a vested interest in your suggestion for multiple sites (I only have one). However, I think that an "upgrade system" itself wouldn''t handle multiple sites or run tests. Instead it could be utilised as part of a continuous integration process or some other such system. Craig On Thu, 23 Jun 2005 5:35 am, Joe Van Dyk wrote:> Hi, > > So, I''m being a good little pragmatic programmer intern. I''ve got > stuff under version control, I''ve got (almost) push-button releases, > I''ve got unit and functional unit tests. > > The thing that''s holding back my perfect one-command release of my > websites is upgrading the database. If the production website already > has some data in it, I don''t want to erase it, obviously. Generally, > I''m just adding fields to tables, or adding an additional field. > Sometimes though, I have to move data around from column to column or > table to table. What''s a good way to handle this pragmatically? > Write a database-migration script for every release? Ew. That > doesn''t handle going back in time, say if I bork a release. > > Other question: Say I have 10 websites up and running. What tools are > out there that will let me monitor them, maybe run some functional > tests on them, and send me a page/email when something blows up?-- Craig Ambrose Web Elements http://www.portallus.com/people/craigambrose/
Hi, The question regarding multiple sites was how to monitor them to see if they are still up, not how to upgrade multiple sites. My release script does do multiple sites well. In essence, I have a yaml configuration file that holds some data about each site (hostname, where on the server they should be published, subversion location, etc). I execute the script with "./publish.rb <site_name> <release_tag>". It logs in to TextDrive via ssh, then runs a series of shell commands that export the release of the site via subversion to a specific directory, creates a symbolic link to that directory, copies a lighttpd.conf file over, and restarts lighttpd. Works pretty well. On 6/22/05, Craig Ambrose <craiga-aW5oDkNkUadaA94nB1n4cRCuuivNXqWP@public.gmane.org> wrote:> > That''s a really interesting issue you''ve raised there Jeo. I''ve noticed that > the approach for handling your database schema which is used in the beta > rails book (of keeping an up to date schema in a file called create.sql) is > all very well for development before an initial release, but it''s hardly > _agile_ as you can''t use it to upgrade. > > I''m working on a rails port of a PHP application, and although I haven''t got > as far as the upgrade system yet, for the original I had a text file to which > I appended changes to the database (whether they were schema changes, or > modifications to the format of live data). A version number in the code, and > one in the database, enabled me to see which lines of SQL needed to be > executed to update the site to the version in the newly uploaded code. I got > into a good habit of expressing all my schema changes as alterations to the > existing database, rather than changing the base schema definition. > > I''m nearly up to this in rails, and I''d be quite happy to produce an ''upgrade > system'' as a small re-usable component (perhaps a generator). My previous > version of this did include putting up an "upgrade in progress" page on the > live site while the rsync was in progress. However, I hadn''t considered > making the progress undo-able. How would that work? > > Finally, I don''t currently have a vested interest in your suggestion for > multiple sites (I only have one). However, I think that an "upgrade system" > itself wouldn''t handle multiple sites or run tests. Instead it could be > utilised as part of a continuous integration process or some other such > system. > > Craig > > On Thu, 23 Jun 2005 5:35 am, Joe Van Dyk wrote: > > Hi, > > > > So, I''m being a good little pragmatic programmer intern. I''ve got > > stuff under version control, I''ve got (almost) push-button releases, > > I''ve got unit and functional unit tests. > > > > The thing that''s holding back my perfect one-command release of my > > websites is upgrading the database. If the production website already > > has some data in it, I don''t want to erase it, obviously. Generally, > > I''m just adding fields to tables, or adding an additional field. > > Sometimes though, I have to move data around from column to column or > > table to table. What''s a good way to handle this pragmatically? > > Write a database-migration script for every release? Ew. That > > doesn''t handle going back in time, say if I bork a release. > > > > Other question: Say I have 10 websites up and running. What tools are > > out there that will let me monitor them, maybe run some functional > > tests on them, and send me a page/email when something blows up? > > > -- > Craig Ambrose > Web Elements > http://www.portallus.com/people/craigambrose/ >
Joe, Anyway you can post that script to http://textsnippets.com/tags/ruby? I''m sure there are quite a few Textdrive users who would love to see it. - Derek On 6/22/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi, > > The question regarding multiple sites was how to monitor them to see > if they are still up, not how to upgrade multiple sites. > > My release script does do multiple sites well. In essence, I have a > yaml configuration file that holds some data about each site > (hostname, where on the server they should be published, subversion > location, etc). I execute the script with "./publish.rb <site_name> > <release_tag>". It logs in to TextDrive via ssh, then runs a series > of shell commands that export the release of the site via subversion > to a specific directory, creates a symbolic link to that directory, > copies a lighttpd.conf file over, and restarts lighttpd. Works pretty > well. > > On 6/22/05, Craig Ambrose <craiga-aW5oDkNkUadaA94nB1n4cRCuuivNXqWP@public.gmane.org> wrote: > > > > That''s a really interesting issue you''ve raised there Jeo. I''ve noticed that > > the approach for handling your database schema which is used in the beta > > rails book (of keeping an up to date schema in a file called create.sql) is > > all very well for development before an initial release, but it''s hardly > > _agile_ as you can''t use it to upgrade. > > > > I''m working on a rails port of a PHP application, and although I haven''t got > > as far as the upgrade system yet, for the original I had a text file to which > > I appended changes to the database (whether they were schema changes, or > > modifications to the format of live data). A version number in the code, and > > one in the database, enabled me to see which lines of SQL needed to be > > executed to update the site to the version in the newly uploaded code. I got > > into a good habit of expressing all my schema changes as alterations to the > > existing database, rather than changing the base schema definition. > > > > I''m nearly up to this in rails, and I''d be quite happy to produce an ''upgrade > > system'' as a small re-usable component (perhaps a generator). My previous > > version of this did include putting up an "upgrade in progress" page on the > > live site while the rsyncwas in progress. However, I hadn''t considered > > making the progress undo-able. How would that work? > > > > Finally, I don''t currently have a vested interest in your suggestion for > > multiple sites (I only have one). However, I think that an "upgrade system" > > itself wouldn''t handle multiple sites or run tests. Instead it could be > > utilised as part of a continuous integration process or some other such > > system. > > > > Craig > > > > On Thu, 23 Jun 2005 5:35 am, Joe Van Dyk wrote: > > > Hi, > > > > > > So, I''m being a good little pragmatic programmer intern. I''ve got > > > stuff under version control, I''ve got (almost) push-button releases, > > > I''ve got unit and functional unit tests. > > > > > > The thing that''s holding back my perfect one-command release of my > > > websites is upgrading the database. If the production website already > > > has some data in it, I don''t want to erase it, obviously. Generally, > > > I''m just adding fields to tables, or adding an additional field. > > > Sometimes though, I have to move data around from column to column or > > > table to table. What''s a good way to handle this pragmatically? > > > Write a database-migration script for every release? Ew. That > > > doesn''t handle going back in time, say if I bork a release. > > > > > > Other question: Say I have 10 websites up and running. What tools are > > > out there that will let me monitor them, maybe run some functional > > > tests on them, and send me a page/email when something blows up? > > > > > > -- > > Craig Ambrose > > Web Elements > > http://www.portallus.com/people/craigambrose/ > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Derek Haynes HighGroove Studios - http://www.highgroove.com Keeping it Simple. 404.593.4879
I just googled around as I would like the same and stumbled upon these: http://www.mysqldiff.org/ which looks like a web based php script more promissing is: http://www.adamspiers.org/computing/mysqldiff/ Which is commandline based and written in perl. It looks like it could be used easily. I will experiment on my next release. Pelle On 6/22/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi, > > So, I''m being a good little pragmatic programmer intern. I''ve got > stuff under version control, I''ve got (almost) push-button releases, > I''ve got unit and functional unit tests. > > The thing that''s holding back my perfect one-command release of my > websites is upgrading the database. If the production website already > has some data in it, I don''t want to erase it, obviously. Generally, > I''m just adding fields to tables, or adding an additional field. > Sometimes though, I have to move data around from column to column or > table to table. What''s a good way to handle this pragmatically? > Write a database-migration script for every release? Ew. That > doesn''t handle going back in time, say if I bork a release. > > Other question: Say I have 10 websites up and running. What tools are > out there that will let me monitor them, maybe run some functional > tests on them, and send me a page/email when something blows up? > > Thanks, > Jeo > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- https://stakeitout.com + Stake out your own micro ventures http://neubia.com + Geek blog http://stakeventures.com + Bootstrapping blog http://SoapBX.com + Get on the box and shout
Pelle Braendgaard wrote:> more promissing is: > > http://www.adamspiers.org/computing/mysqldiff/ > > Which is commandline based and written in perl. It looks like it could > be used easily. I will experiment on my next release.Yes, I''m using it. It''s *very* powerful (but not compatible with 4.1, afaik) My approach when coming to upgrading database schemas is based on a "patches" concept. I''ll try to explain it: - I use a table in my database that contains the history of the subsequent patches applied to the database itself: create table db_patch (applied_on datetime, patch_name varchar (100)); - I use three different files for my db (they are "run" in this order, giving a fresh up to date installation): schema_install.sql - The "crude" DDL (CREATEs and such) dump_install.sql - To populate same table (countries, for example) patch_install.sql - to update db_patch, so the newly created db could be "up to date with the latest patch" When I modify the database schema, I run a (interactive) Perl script that: - calls [a slighlty modified version of] mysqldiff to diff my actual db on localhost against the current schema_install.sql file. This produces all the series of ALTER, CREATE or DROP needed to line up the db - wraps all these DDL within calls to a custom PHP function - re-generates the schema_install.sql (calling mysqldump --no-data) - inserts a new record in the db_patch table - updates "updates.php", so that my co-worker, after my commit, could run it, lining up his database with mine (his db lacks my newly created patch, indeed) That way we can happily going on drop, create, alter, without the fear to "lost something in the way". Anyone being interested, I''ll drop those scripts somewhere. -- Claudio Cicali http://www.flexer.it GPG Keyid: 1024D/555D25CE ''Nos patriae finis et dulcia linquimus arva''