This way madness lay.
I you don't have data in the database, then it's easy:
Just drop the old database, and use your new script to create it again.
The end result is the same as if you have used alter table's.
O.k., but you probably do have data in the database that you want to preserve.
But then, it's not a well-posed problem, as mathematicians would say.
The real world equivalent of this is that whichever solution you pick up,
you'll be flamed.
Assume just a very simple difference:
Database D1 have table T1 with columns C1, C2.
Database D2 have table T1 with columns C1, C2, C3.
You'll leap up and say: so the difference is the addition of C3.
But, couldn't it be that you decided to rename C2 into C3 and add another C2?
How are you going to decide which column is new and which column was there already?
By column names? By data types? By the combination of those two?
Who has the right answer?
Most important though, you didn't say what should happen to the _data_
in the columns - and you can't say that from just looking
at the database structures.
Even if you settle on a way to decide which column is really new,
how do you know what should be put in a new column?
A default value? You probably didn't create an extra column to hold just a default
value.
So you need to do some extra work in order to assign the right values to the new
column.
O.k., so the statement is:
if you don't have data in it, why not drop it and create it anew.
And if you do have data in the database, creating a mapping script
from just the difference of the database structures is bound to go wrong.
Jan
[EMAIL PROTECTED] wrote:
>
> Hello Cal,
>
> I don't think there is a "magic bullet" for what you want. Although I'm sure some
>sort of Perl script could do a comparison and create the proper ALTER TABLE SQL to
>make the two tables equal.
>
> I usualy just copy tables using the CREATE TABLE syntax (as below) and make the
>manual alterations as needed.
>
> Regards,
>
> - Scott
>
> > Database in Test:
> >
> > Table1
> > --------
> > Table1ID
> > ========
> > myField1 varChar(20)
> > myField2 int
> > myFiled3 varChar(40)
> > --------
> >
> > Database in Prod:
> > Table1
> > --------
> > Table1ID
> > ========
> > myField1 varChar(60)
> > myField2 int
> > myField3 varChar(40)
> > myField4 int
> > --------
> >
> > I'd like something that compares these two databases. (Either the databases
> > themselves or a mysqldump of the databases and comes up with a sql script:
> >
> > Alter table Table1 add myField4 int;
> > Alter table Table1 change myField1 myField1 varChar(60);
> >
> > Yes, I can do it by eyeballing it...I'm just too lazy. :)
> >
> > Thanks for replying. Hope I've made things clearer.
> >
> > Cal
> > http://www.calevans.com
> >
> >
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> > Sent: Sunday, January 21, 2001 1:48 PM
> > To: Cal Evans
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: Database structure DIFF
> >
> >
> >
> > I'm not sure what you mean by "structural changes".
> >
> > If the the two table have the same number of columns and they are in the
> > same order, you can do something like what I have explained below. It you
> > included the table info from prod and test I could help you better (DESC
> > prod;).
> >
> >
> >
> > # Create the empty shell of test (all column definitions);
> >
> > CREATE TABLE prod_new
> > SELECT * FROM test
> > WHERE 1=2;
> >
> > # populate prod_new with prod data
> > INSERT INTO TABLE prod_new
> > SELECT * FROM prod;
> >
> > - Verify the prod_new is correct
> > - DROP prod
> > - RENAME prod_new to prod.
> >
> > - Scott
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > > Pardon the newbie question but is there a tool available for MySQL that I
> > > can give it 2 databases and it give me the ALTER TABLE statements
> > necessary
> > > to make 2 identical to 1?
> > >
> > > Specifically, I have a test and prod database. I've made structural
> > changes
> > > to test and want to propagate them to prod. Is there an easy way to do
> > > this?
> > >
> > > TIA,
> > > Cal
> > >
> > > Cal
> > > http://www.calevans.com
> > >
> > >
> > >
> > > ---------------------------------------------------------------------
> > > Before posting, please check:
> > > http://www.mysql.com/manual.php (the manual)
> > > http://lists.mysql.com/ (the list archive)
> > >
> > > To request this thread, e-mail <[EMAIL PROTECTED]>
> > >
> > --
> > --------------------------------------
> > Scott A. Gerhardt P.Geo.
> > Gerhardt Information Technologies
> > [EMAIL PROTECTED]
> > --------------------------------------
> >
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
> --
> --------------------------------------
> Scott A. Gerhardt P.Geo.
> Gerhardt Information Technologies
> [EMAIL PROTECTED]
> --------------------------------------
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
