I handle this by having each developer put the scripts into a folder named:
YYYYMMDD Where the folder name is the date of the next scheduled release. Under that folder each script is named YYYYMMDD_NNN_DESCRIPTION.sql Where YYYYMMDD in this case is the date the script was created and NNN is the sequence # of the script if there is more than one script created on a single day. When release time comes, we simply combine all scripts in that release folder. If, in the event, we have to bring a really old database up to date, we simply combine all scripts in all folders since the last release date on that database up to now. -JW On Fri, Jan 22, 2010 at 4:12 PM, Daevid Vincent <dae...@daevid.com> wrote: > Huh? This makes no sense. > > There is one single file "UPDATES.sql" that has ALL the changes in it. > > In order to prefix a line, it would need to be commented. It would also > need to be one single change per line. Highly inefficient. > > This still doesn't solve the fact that different developers checkout or > update the code on different days/times. How would you automate the fact > that b/c I did an update today, which specific UPDATE.sql commands need to > be run since some have already been after the last update. You'd need to > store a file somewhere with a date stamp. > > Now, we could split each block of SQL commands into separate files and the > script could check file dates, but you still have to save off the last time > you updated somewhere. > > Anyways, it's just easier for a developer to mentally keep track of what > the last SQL they remember running was. And if they forgot, all they have > to do is check their version of the database schema against what the SQL > command wants to do. A "diff" if you will. > > ÐÆ5ÏÐ > Light travels faster than sound. This is why some people appear bright > until you hear them speak. > > _____ > > From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De > Meersman > Sent: Friday, January 22, 2010 1:06 AM > To: Daevid Vincent > Cc: mysql@lists.mysql.com; Price, Randall > Subject: Re: Best way to synchronize two database schemas > > > Simple: prefix the change files with yyyymmddhhmm formatted timestamps, so > they sort correctly :-) > > > On Thu, Jan 21, 2010 at 11:31 PM, Daevid Vincent <dae...@daevid.com> > wrote: > > > Exactly what Johan said. > > I keep structure like so: > > develo...@mypse /var/www/dart2/UPDATES $ ll > -rw-rw-rw- 1 developer developer 551097 2009-12-22 23:16 > airports_city_country.sql > drwxrwxrwx 2 developer developer 4096 2010-01-21 04:51 CVS > -rw-rw-rw- 1 developer developer 3063 2009-07-15 01:40 fix_airports.php > -rw-r--r-- 1 developer developer 23414 2010-01-21 03:52 > ps_access_to_mysql.sql > -rw-rw-rw- 1 developer developer 12259 2010-01-06 05:22 UPDATES.sql > > Any and all changes to DB schema are in the UPDATES/UPDATES.sql file and > each are commented with the date of the change, who did it, and why. This > file/dir is part of your repository, so as each developer checks out, they > would run the appropriate part of the script as well. I've not yet found a > good (and safe) way to automate this process. > > /* 2009-06-01 [dv] fix the privileges for various users as they were all > whacked out > * http://dev.mysql.com/doc/refman/5.0/en/grant.html > */ > REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'foo'@'10.10.10.%'; > ... > > If you're using Subversion, you may find my "Subversion Flagged Update" > script helpful... > http://daevid.com/content/examples/snippets.php > > I also can vouche for SQLYog. Aside from being the absolute BEST mySQL GUI > I've ever used, it has a feature to create the schema differences between > two live databases. I've used it before to get a DEV and PROD server in > sync, so that I could then implement the above methodology. > > > > -----Original Message----- > > From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On > > Behalf Of Johan De Meersman > > Sent: Thursday, January 21, 2010 10:35 AM > > To: Price, Randall > > Cc: mysql@lists.mysql.com > > Subject: Re: Best way to synchronize two database schemas > > > > The best way is to keep track of all individual changes to your > > staging environment, including fire-and-forget style scripts; and > > apply those to your production environment as needed. This is part of > > the process of change management, and generally a very good idea :-) > > > > Lacking that, there are several tools that can generate a differential > > script to do exactly this. I don't really use them, but I seem to > > remember that SQLyog and some expensive but excellent Quest tool could > > do it. > > > > On 1/21/10, Price, Randall <randall.pr...@vt.edu> wrote: > > > I have a two databases, one in a production environment > > (let's call it > > > db_prod) and the other in a testing environments (Let's > > call it db_test). > > > > > > What is the best way to synchronize the database schemas? > > db_test has had a > > > few indexes and constraints added to several tables and I > > need to generate a > > > MySQL script to apply these changes to db_prod. So > > basically I want to dump > > > the schemas of the two database, compare, and generate the > > necessary script > > > to apply to db_prod. > > > > > > Thanks, > > > Randall Price > > > > > > > > > > > > -- > > Bier met grenadyn > > Is als mosterd by den wyn > > Sy die't drinkt, is eene kwezel > > Hy die't drinkt, is ras een ezel > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?unsub=dae...@daevid.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > > > > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel > > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
