From mboxrd@z Thu Jan 1 00:00:00 1970 Received: from lists.gentoo.org ([140.105.134.102] helo=robin.gentoo.org) by nuthatch.gentoo.org with esmtp (Exim 4.60) (envelope-from ) id 1Fqx8K-0000YH-FC for garchives@archives.gentoo.org; Thu, 15 Jun 2006 19:04:00 +0000 Received: from robin.gentoo.org (localhost [127.0.0.1]) by robin.gentoo.org (8.13.7/8.13.6) with SMTP id k5FJ1XV1002917; Thu, 15 Jun 2006 19:01:33 GMT Received: from popmail.jettissystems.com (popmail.jettissystems.com [38.118.146.212]) by robin.gentoo.org (8.13.7/8.13.6) with ESMTP id k5FIt5wI020382 for ; Thu, 15 Jun 2006 18:55:06 GMT Received: from [192.168.0.104] (c-69-181-70-226.hsd1.ca.comcast.net [69.181.70.226]) by popmail.jettissystems.com (Postfix) with ESMTP id AAA2456D485 for ; Thu, 15 Jun 2006 11:55:04 -0700 (PDT) Message-ID: <4491AD08.3030009@badapple.net> Date: Thu, 15 Jun 2006 11:55:04 -0700 From: kashani User-Agent: Thunderbird 1.5.0.4 (Windows/20060516) Precedence: bulk List-Post: List-Help: List-Unsubscribe: List-Subscribe: List-Id: Gentoo Linux mail X-BeenThere: gentoo-user@gentoo.org Reply-to: gentoo-user@lists.gentoo.org MIME-Version: 1.0 To: gentoo-user@lists.gentoo.org Subject: Re: [gentoo-user] [OT] Looking for a tool to produce 'reverse' SQL References: <20060615181537.44942.qmail@web53208.mail.yahoo.com> In-Reply-To: <20060615181537.44942.qmail@web53208.mail.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Archives-Salt: 21c2c355-787a-4390-90ef-6eca2f9158b3 X-Archives-Hash: 793093e81b44aeb106bc51e44c3a92f7 Benjamin Blazke wrote: > Hi, > > I'm looking for a tool that given an existing (base) > database schema and an 'update patch' DDL .sql script > on input would produce a 'reverse' script that could > be used to undo the changes done by the patch. For > example: > > base.sql: > CREATE TABLE xxx (...); > > patch.sql: > CREATE TABLE yyy (...); > ALTER TABLE xxx ADD COLUMN aaa ...; > ALTER TABLE xxx CHANGE column bbb ... > > reverse.sql: > DROP TABLE yyy; > ALTER TABLE xxx DROP column aaa; > ALTER TABLE xxx CHANGE column bbb original base schema> > > The purpose of this exercise is to have a production > database (MySQL) server that needs to update its > schema once in a while to reflect the changes in the > related application without the need to recreate the > schema from scratch (and possibly losing data). The > reverse.sql script could be later used to rollback the > schema changes at any time, even rollback multiple > patches. > > How do people generally solve this? I'm sure this must > be a fairly common problem. The tool you're looking for is called a DBA. :-) On a more serious note: I normally do the schema changes and table updates followed by updating the code on the servers. Once the code is live we're pretty much stuck with it because it often relies on the new fields or uses the new data we populated. I could revert back to the original data, but we'd lose any new data that came in after the upgrade. If your changes were minor it wouldn't be too hard to manually reverse. On the other hand anything very complicated to reverse where you'd want a tool to do it is likely going to fall into my situation where the new data isn't going to work in the old tables, the old application isn't going to like the new data or tables, and so on. We get around it by doing lots and lots of testing. I probably run through the schema and data updates five or so times depending on the complexity on the changes along with continual QA as the new application is being built in the staging environment. kashani -- gentoo-user@gentoo.org mailing list