Hi!
> I'd like to find a way of comparing the two MySQL database strcutures
> and getting a list of the differences, anyone know of any
> modules/methods for doing this?
Here's more or less what some commercial tools do:
**pseudocode start**
get all lines, split them into a datastructure like this
$tables[x}->{"dev" or "orig"}->line[x]
foreach $table (@tables) {
if($table->{"prod"} is constraint or index) {
"drop bla bla bla"
}
}
foreach $table (@tables) {
if($table->{"prod" or "dev"} is constraint or index) {
next
}
if(all lines $tables->{"dev"} eq all lines $tables->{"dev"} eq) {
next;
}
if(dev && !prod) {
"create bla bla bla"
next
} elsif(!dev && prod) {
"drop bla bla bla"
next
}
if(dev has lines not in prod) {
for every extra line: "alter table add bla bla bla");
}
if(prod has lines not in dev) {
for every extra line: "alter table drop bla bla bla");
}
}
foreach $table (@tables) {
if($table->{"dev"} is constraint or index) {
"create bla bla bla"
}
}
**pseudocode end**
I'd say, as long as you got that reader/parser to give you a nice data
struct, you should get a beta working in an hour or two.
Tip: You might notice that there is normally a newline and/or semicolon at
the end of each statement. Also the TYPE and NAME of the object should more
or less always be second and third word of every statement such as in
"create TABLE hello".
I didn't try with mysqldump, but most database tool write the fields of any
"create table" on a line-by-line basis, effectively pre-parsing the
statement for you.
Ok, and here's the reason i'm dropping indexes and constraints at the top
and re-creating them at the end: Constraints can be a bitch, when you're
altering tables, because you're having to do that in the proper{tm} order...
Indexes: After altering tables, it might be a good idea to re-create them
anyway. I've seen databases (mysql, postgresql, oracle...) where indexes
silently failed after excessive "alter table"s. Meaning: Performance breaks
down, cause the db decides to do full table scans every f...ing time...
PS: You also might want to peek at DBD::Anydata, maybe there's something in
there to help you,
PPS: If you can't get it working, post two example dumps (without data, just
the creates), and i'll see what i can do.
LLAP & LG
Rene
--
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1 Visit <http://www.ebb.org/ungeek/> to decode
GCS d- s:- a-- C++$ UBLA*++++$ P++++$ L+$ !E !W+++$ N+ o+ K--?
w++$ !O M+ V-- PS PE Y+ PGP+ t+ 5 X- !R tv b+++ DI-- D++ G e h-- r-- y+
------END GEEK CODE BLOCK------
-----------------------------------------
This E-Mail was sent through MagicMail
Download our Jump'n'Run "BlinkenSisters":
http://www.blinkensisters.org
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
