Re: [gentoo-user] [OT] Looking for a tool to produce 'reverse' SQL

[kashani]

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 <get this from the
> 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@g.o mailing list