Merging 2 databases schemas WordPress
- SOLVED
This is more of a PHP/MySql question than a WordPress question.
I once had to merge 2 different versions of a CMS. I think these were deviant mutations of some Drupal project gone bad. The company had been using Drupal, but each city was allowed to customize, and Chicago did one thing, in isolation, for 3 years, and New York did another thing, for 3 years, and the schemas ended up different, and needed to be merged. So I wrote a PHP script to merge them. I started with:
show tables;
from database A and then database B, and then I copied (to A) the tables that were in B but not A. But A also had many tables that B had, but the columns were different, so I had to loop over each table and get the names of the columns (I forget how I did this -- I assume I ran the query on the Information Schema table).
Anyway, I eventually got the script so it coped all tables and all columns and all column types.
That was 2 years ago. I no longer have the script. I need again. I am wondering if there is an open source version that does the same?
I asked about this before and got some interesting suggestions, specific to MySql. However the new project will probably be using PostGreSql, so if anyone knows of a script that can do MySql to PostGre conversions, I'd be grateful for the suggestion.
Answers (2)
Fahad Murtaza answers:
I remmeber converting an Access database to MYSQL and there were a few changes I had to do in database scheme. One that I remember was date/ time and varchar columns.
I did some googling based on the same idea and came up with this link
http://tapoueh.org/blog/2013/11/12-migrating-sakila
Looks like they used pgloader to do all the schema conversions.
So the pgloader command should be something like
LOAD DATABASE
FROM mysql://root@localhost/mysql_db_to_be_converted_to_pgsql
INTO postgresql://localhost:54393/mysql_db_after_converted_into_pgsql
WITH include drop, create tables, no truncate,
create indexes, reset sequences, foreign keys
SET maintenance_work_mem to '128MB', work_mem to '12MB',
search_path to 'mysql_db_after_converted_into_pgsql' /* migrate to a specific schema */
CAST type datetime to timestamptz
drop default drop not null using zero-dates-to-null,
type date drop not null drop default using zero-dates-to-null,
type year to integer
BEFORE LOAD DO
$$ create schema if not exists mysql_db_after_converted_into_pgsql; $$;
Lawrence Krubner comments:
That would work for a straight port of MySql to PostGre, so that is useful to know about, but I would need to first merge the 2 MySql databases, so I will keep that portion of the question open for any other suggestions. It does look like if I can merge the 2 MySql databases, this pgloader command makes the remaining steps easy.
Fahad Murtaza comments:
Hi Lawrence
I'd use a semi automatic method to do this. You mentioned that
<blockquote>from database A and then database B, and then I copied (to A) the tables that were in B but not A. But A also had many tables that B had, but the columns were different, so I had to loop over each table and get the names of the columns (I forget how I did this -- I assume I ran the query on the Information Schema table). </blockquote>
that seems like the best way to do it.
I have used the diff merge utility to merge such tables before. For the tables where the names match and there are primary and foreign keys to take care of, using the information_schema and a bash script is a good option and is quite straight forward.
First you get every key-column in the database and the table in which it occurs, and then update each of those columns.
echo Incrementing every primary and foreign key by $increment
# Get the table name and column name for every key from the information_schema
select_constraints_sql="select TABLE_NAME, COLUMN_NAME from KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA = 'MyDB'"
# Place the query results in an array
data=( $(mysql -e "$select_constraints_sql" -sN --user=$username --password=$passwd information_schema) )
# Step through the tables and keys and update each, with foreign key checks disabled
# Foreign key checks must be disabled at each step
ignore_fks_sql="SET FOREIGN_KEY_CHECKS = 0"
cnt=${#data[@]}
for (( i=0 ; i < cnt ; i=i+2 ))
do
update_key_sql="$ignore_fks_sql; UPDATE ${data[$i]} SET ${data[$i+1]} = ${data[$i+1]} + $increment"
mysql -v -e "$update_key_sql" --user=$username --password=$passwd MyDB
done
# This is just me being a bit pedantic
check_fks_sql="SET FOREIGN_KEY_CHECKS = 1"
mysql -v --user=$username --password=$passwd -e "$check_fks_sql" MyDB
I have tried that, it assumes that the database is well designed and all FKs too are.
So, that might not be the best solution.
[[LINK href="http://www.devart.com/dbforge/mysql/datacompare/"]]http://www.devart.com/dbforge/mysql/datacompare/[[/LINK]]
is relatively cheap and has a good trial period as well.
If you want coding control over the process, percona toolkit seems a great option. Here is the relevant documentation:
[[LINK href="http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html"]]http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html[[/LINK]]
I have used MySQL Workbench to analyse database schemas in case, I need to use any other tool for the actual merging process.
Final suggestion: dbforge seems like a great tool. I have yet to try it myself though!
Fahad Murtaza comments:
I have used Navicat for MySQL successfully for similar application before and I recommend it. Their price is good too and all the tools are developed nicely. If Navicat for PostgreSQL is like Navicat for MySQL, your final import after you merge the databases should be minutes away.
Ross Wilson answers:
I have used this tool before to do a database merge, they have a free 30 day fully-functional trial:
[[LINK href="http://www.altova.com/databasespy/database-structure-compare-tool.html"]]http://www.altova.com/databasespy/database-structure-compare-tool.html[[/LINK]]