use fixtures, then you can dump the db and create it again and load test data automatically of course you'll have to adjust the fixtures when you change something, but its much easier then altering tables On Nov 9, 2007 12:24 PM, Thierry <thierryschellenb...@gmail.com> wrote:
I think there are many programs out there to sync the structure of two databases, but I simply don't know what to look for. Any help would be appreciated.
I've heard that Premium Soft's Navicat MySQL has a good utility for syncing the structure of two databases. Unfortunately I haven't used it myself yet. An ex-co-worker of mine swore by it.
Thierry wrote: > I think there are many programs out there to sync the structure of two > databases, but I simply don't know what to look for. > Any help would be appreciated.
On Nov 9, 3:24 am, Thierry <thierryschellenb...@gmail.com> wrote:
> Is there any way to generate the sql needed to bring the structure of > the existing database up to the specification in the schema.yml?
> How do you guys handle db scheme changes? Any best practices?
It's probably too for this now, but when I make changes to my schema, I echo those changes in an SQL file that does the appropriate ALTERS to my database. This does require dual entry though, I.E. you make changes to the schema AND to a SQL update file.
My method is to keep the SQL update files in /data/update path. I name my files after the version that it brings the database to. E.g. If my production application is at version 1.0.0 and I've got a bunch of development changes and now I want to bring version 1.0.0 up to date with my development version (which I have tagged say 1.0.1)... my SQL update script is named /data/update/1.0.1-update.sql
This way I can also ensure that I upgrade in the proper sequence, incase I tag a few versions of my development copy, but for some reason I don't put these into production for a few tags. E.g. My development version is at 1.0.1 and since then I've developed a 1.0.2 and a 1.0.3 and a 1.0.4 and now I want to upgrade my production from 1.0.1 to 1.0.4... my /data/update/ directory contains 1.0.1- update.sql, 1.0.2-update.sql, and 1.0.4-update.sql... so I run 1.0.2- update.sql (not 1.0.1 since I am already at that version) then 1.0.4- update.sql (there is no 1.0.3-update.sql perhaps because that version had no model changes, just logic changes).
Another benefit that I like of create update.sql scripts, is that sometimes I might make a drastic change to the model (like combining two tables, or splitting one table into two, for one reason or another). This is hard to change the structure AND the data using some kind of automated tool. It would require some SELECTs and INSERTs and UPDATEs in addition to the ALTERs. So as long as I have been cataloging my changes in my update scripts and thinking about it as I make changes to my model, I am fine and it is rather quite easy to perform production upgrades.
To rebuild with the new schema: rm config/generated*.xml ./symfony propel-13-build-all ./symfony propel-13-insert-sql
To reload the data: mysql -u MYUSERNAME --password=MYPASSWORD MYDATABASE < sql/ MYDATABASE.insert.sql
This handles adding fields very well. It does NOT handle renaming or removing fields. If you want to remove / rename fields, do that operation on the database before doing the first save. Otherwise, your sql file will be filled with inserts pointing to non-existent fields.
Mike
On Nov 9, 4:04 pm, Jonotron <jonot...@gmail.com> wrote:
> On Nov 9, 3:24 am, Thierry <thierryschellenb...@gmail.com> wrote:
> > Is there any way to generate the sql needed to bring the structure of > > the existing database up to the specification in the schema.yml?
> > How do you guys handle db scheme changes? Any best practices?
> It's probably too for this now, but when I make changes to my schema, > I echo those changes in an SQL file that does the appropriate ALTERS > to my database. This does require dual entry though, I.E. you make > changes to the schema AND to a SQL update file.
> My method is to keep the SQL update files in /data/update path. I > name my files after the version that it brings the database to. E.g. > If my production application is at version 1.0.0 and I've got a bunch > of development changes and now I want to bring version 1.0.0 up to > date with my development version (which I have tagged say 1.0.1)... my > SQL update script is named /data/update/1.0.1-update.sql
> This way I can also ensure that I upgrade in the proper sequence, > incase I tag a few versions of my development copy, but for some > reason I don't put these into production for a few tags. E.g. My > development version is at 1.0.1 and since then I've developed a 1.0.2 > and a 1.0.3 and a 1.0.4 and now I want to upgrade my production from > 1.0.1 to 1.0.4... my /data/update/ directory contains 1.0.1- > update.sql, 1.0.2-update.sql, and 1.0.4-update.sql... so I run 1.0.2- > update.sql (not 1.0.1 since I am already at that version) then 1.0.4- > update.sql (there is no 1.0.3-update.sql perhaps because that version > had no model changes, just logic changes).
> Another benefit that I like of create update.sql scripts, is that > sometimes I might make a drastic change to the model (like combining > two tables, or splitting one table into two, for one reason or > another). This is hard to change the structure AND the data using > some kind of automated tool. It would require some SELECTs and > INSERTs and UPDATEs in addition to the ALTERs. So as long as I have > been cataloging my changes in my update scripts and thinking about it > as I make changes to my model, I am fine and it is rather quite easy > to perform production upgrades.
> To rebuild with the new schema: > rm config/generated*.xml > ./symfony propel-13-build-all > ./symfony propel-13-insert-sql
> To reload the data: > mysql -u MYUSERNAME --password=MYPASSWORD MYDATABASE < sql/ > MYDATABASE.insert.sql
> This handles adding fields very well. It does NOT handle renaming or > removing fields. If you want to remove / rename fields, do that > operation on the database before doing the first save. Otherwise, > your sql file will be filled with inserts pointing to non-existent > fields.
> Mike
> On Nov 9, 4:04 pm, Jonotron <jonot...@gmail.com> wrote: > > On Nov 9, 3:24 am, Thierry <thierryschellenb...@gmail.com> wrote:
> > > Is there any way to generate the sql needed to bring the structure of > > > the existing database up to the specification in the schema.yml?
> > > How do you guys handle db scheme changes? Any best practices?
> > It's probably too for this now, but when I make changes to my schema, > > I echo those changes in an SQL file that does the appropriate ALTERS > > to my database. This does require dual entry though, I.E. you make > > changes to the schema AND to a SQL update file.
> > My method is to keep the SQL update files in /data/update path. I > > name my files after the version that it brings the database to. E.g. > > If my production application is at version 1.0.0 and I've got a bunch > > of development changes and now I want to bring version 1.0.0 up to > > date with my development version (which I have tagged say 1.0.1)... my > > SQL update script is named /data/update/1.0.1-update.sql
> > This way I can also ensure that I upgrade in the proper sequence, > > incase I tag a few versions of my development copy, but for some > > reason I don't put these into production for a few tags. E.g. My > > development version is at 1.0.1 and since then I've developed a 1.0.2 > > and a 1.0.3 and a 1.0.4 and now I want to upgrade my production from > > 1.0.1 to 1.0.4... my /data/update/ directory contains 1.0.1- > > update.sql, 1.0.2-update.sql, and 1.0.4-update.sql... so I run 1.0.2- > > update.sql (not 1.0.1 since I am already at that version) then 1.0.4- > > update.sql (there is no 1.0.3-update.sql perhaps because that version > > had no model changes, just logic changes).
> > Another benefit that I like of create update.sql scripts, is that > > sometimes I might make a drastic change to the model (like combining > > two tables, or splitting one table into two, for one reason or > > another). This is hard to change the structure AND the data using > > some kind of automated tool. It would require some SELECTs and > > INSERTs and UPDATEs in addition to the ALTERs. So as long as I have > > been cataloging my changes in my update scripts and thinking about it > > as I make changes to my model, I am fine and it is rather quite easy > > to perform production upgrades.
> To rebuild with the new schema: > rm config/generated*.xml > ./symfony propel-13-build-all > ./symfony propel-13-insert-sql
> To reload the data: > mysql -u MYUSERNAME --password=MYPASSWORD MYDATABASE < sql/ > MYDATABASE.insert.sql
> This handles adding fields very well. It does NOT handle renaming or > removing fields. If you want to remove / rename fields, do that > operation on the database before doing the first save. Otherwise, > your sql file will be filled with inserts pointing to non-existent > fields.
> Mike
> On Nov 9, 4:04 pm, Jonotron <jonot...@gmail.com> wrote: >> On Nov 9, 3:24 am, Thierry <thierryschellenb...@gmail.com> wrote:
>>> Is there any way to generate the sql needed to bring the structure of >>> the existing database up to the specification in the schema.yml? >>> How do you guys handle db scheme changes? Any best practices? >> It's probably too for this now, but when I make changes to my schema, >> I echo those changes in an SQL file that does the appropriate ALTERS >> to my database. This does require dual entry though, I.E. you make >> changes to the schema AND to a SQL update file.
>> My method is to keep the SQL update files in /data/update path. I >> name my files after the version that it brings the database to. E.g. >> If my production application is at version 1.0.0 and I've got a bunch >> of development changes and now I want to bring version 1.0.0 up to >> date with my development version (which I have tagged say 1.0.1)... my >> SQL update script is named /data/update/1.0.1-update.sql
>> This way I can also ensure that I upgrade in the proper sequence, >> incase I tag a few versions of my development copy, but for some >> reason I don't put these into production for a few tags. E.g. My >> development version is at 1.0.1 and since then I've developed a 1.0.2 >> and a 1.0.3 and a 1.0.4 and now I want to upgrade my production from >> 1.0.1 to 1.0.4... my /data/update/ directory contains 1.0.1- >> update.sql, 1.0.2-update.sql, and 1.0.4-update.sql... so I run 1.0.2- >> update.sql (not 1.0.1 since I am already at that version) then 1.0.4- >> update.sql (there is no 1.0.3-update.sql perhaps because that version >> had no model changes, just logic changes).
>> Another benefit that I like of create update.sql scripts, is that >> sometimes I might make a drastic change to the model (like combining >> two tables, or splitting one table into two, for one reason or >> another). This is hard to change the structure AND the data using >> some kind of automated tool. It would require some SELECTs and >> INSERTs and UPDATEs in addition to the ALTERs. So as long as I have >> been cataloging my changes in my update scripts and thinking about it >> as I make changes to my model, I am fine and it is rather quite easy >> to perform production upgrades.
Is everybody not aware of http://trac.symfony-project.com/wiki/sfPropelMigrationsLightPlugin ? It will not solve your problem now but use mysql diff tool and then create a migration that you an run in production. And from then on use migrations everytime you make schema changes.
Thierry wrote: > Is there any way to generate the sql needed to bring the structure of > the existing database up to the specification in the schema.yml?
> How do you guys handle db scheme changes? Any best practices?
I like the approach of dumping the mysql data. It is the probably the only solutions, considering propel. Now have a little pake task: propel-build-all-save-mysql
which does mysqldump, propel-build-all, load dump in that order It's still quite raw, but working nicely.
