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

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

[Benjamin Blazke]

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.

Thanks.

Ben



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
-- 
gentoo-user@gentoo.org mailing list

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@gentoo.org mailing list

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

[kashani]

Benjamin Blazke wrote:
> 
> --- kashani <kashani-list@badapple.net> wrote:
> 
>> The tool you're looking for is called a DBA. :-)
> 
> I see. So it's up to QA to test extensively and up to
> the DBA to recover from a disaster.
> 
> I hoped there would be a more automated solution but
> it seems that it's not really doable. Thanks for such
> a quick answer ;-) 

	That's pretty much the way we've been doing it, but if there is a 
better way I'd like to hear it too as I'm a poor imitation of a DBA. 
However I don't see any easy solutions for combined application, data, 
schema change rollbacks especially when changes to one cause 
dependencies in others.

	As an illustration you change u_user.login_name to varchar(64) from 
varchar(32). Users start creating longer users names. A few hours later 
you find some problems in how your application handles longer names. If 
you needed to rollback the alter table command is easy, but some of your 
data would now be invalid. Rather than rollback the easier fix is to 
update the application and hopefully the change is a single file update.

	I still think there are cases when you could rollback, but they'd have 
to be so simple that having a tool to generate the sql would be overkill.

Ramin
-- 
gentoo-user@gentoo.org mailing list

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

[Daniel Iliev]

On Thu, Юни 15, 2006 9:15 pm, 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.
>
> Thanks.
>
> Ben
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
> --
> gentoo-user@gentoo.org mailing list
>
>

If we're talking about a production system its very very bad idea to patch it directly.
The right way is to have an offline mirror of the system where you apply the
patches, test and only if they work fine, you apply the update on the production
system. If the patches are not OK, you just roll them back, restore from a backup,
or copy the live system over the offline one.

-- 
Best regards,
Daniel

-- 
gentoo-user@gentoo.org mailing list