MySQL database structure comparation

Discusions not-related to our Components

Moderators: gto, cipto_kh, EgonHugeist

zippo
Silver Boarder
Silver Boarder
Posts: 322
Joined: 12.10.2005, 18:01
Location: Slovenia

MySQL database structure comparation

Postby zippo » 23.10.2006, 11:08

Hello!

Does anyone know how to compare two database (on the same server) structures? If there's any Delphi code it would be great.. :)

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 23.10.2006, 12:31

What do you mean?
Only mysql or db independent?
If mysql: 5 or all versions?
I suppose you want to know what tables/fields are in database A and not in database B and the other way around.


For mysql 5 I would try to write some queries on the information_schema tables. I think some queries must be sufficient.

Using Zeoslib : I would try to use 2*2 metadata components on 2 different connections. For each table in every connection the second component can be used to compare details.

I did not actually try those solutions, however.

Mark

zippo
Silver Boarder
Silver Boarder
Posts: 322
Joined: 12.10.2005, 18:01
Location: Slovenia

Postby zippo » 23.10.2006, 12:47

I will show you what I mean:

I have two databases of the same application, but different versions. This means that some tables are missing, some fields are missing, some fields are different (type, size) or some indexes are missing/different.

I would like to see the differences between the two databases.

It can be for MySQL 5 (as the other versions are going to disappear in time).

The idea is that I will need to write it, but if it is written, then why to do the same task twice.. :)

Jay
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 03.03.2006, 03:16
Location: Mexico

Postby Jay » 23.10.2006, 17:08

You will have to do 2 conections, 1 for each database. On the base database (the one that is complete) you first do a query (SHOW TABLES) and compare it to a similar query (SHOW TABLES) of the other DB. The diference are the tables that are missing. You then use this list of tables anq do a loop. For each table you can use diferent queries (SHOW FIELDS), (SHOW INDEX) etc. and compare the results to find the missing fields, indexes or any diference in them.

Once you have this routine, it will also apply to any future changes.

Regards,

Jay

zippo
Silver Boarder
Silver Boarder
Posts: 322
Joined: 12.10.2005, 18:01
Location: Slovenia

Postby zippo » 23.10.2006, 17:28

Ok, thanks!

I'll make the routines - BTW: If anyone needs them, just drop me a PM or mail.. :)

waheed
Junior Boarder
Junior Boarder
Posts: 26
Joined: 18.11.2005, 21:41

Postby waheed » 11.03.2007, 05:50

I don't know how far you got in the routines. But just to let you know:

http://www.sqlmanager.net/en/products/mysql/dbcomparer


Return to “Off Topic”

Who is online

Users browsing this forum: No registered users and 1 guest