DBTNG-format database dump and import

Subscribe. I would definitely like this to happen. The Rails world has something similar called YAMLDB. Having this could strongly foster the adoption of all the non-MySQL options we now provide :)

I'm going to tentatively put this on my plate, though it might be a while before I start. Assistance welcome.

It looks like Josh Waihi already did the hard part. Thanks, Josh.

Subscribe, as Greg mentioned, I've done it before (MySQL -> PostgreSQL and back) so I could patch something up I think.

Note sure where Josh code comes from, but most of the code to dump databases in a portable format is already available in D7 core (in the scripts/ directory). We wrote that with chx one year ago to power the core upgrade tests.

I don't write a dump out at all but instead, I migrate the data directory from one database to another using DBTNG. Exporting to a file would be useful I guess but also the file could become very large. Lets face it, PHP as a dump file is not a very optimized approach. The beauty of DBTNG is that this task in Drupal7 is not very hard at all (read my blog).

I want to support both. Thanks for the pointer to the scripts directory; I'll be sure to check there.

Subscribing.

I reviewed the dump-database-d6.sh script, which I now realize is actually a dbtng-dump.php script, that will convert any dbtng-accessible database into a db-neutral php file (just like #0 says -- I am now un-confused).

In #8, I thought that I would use Josh's code to migrate, and the d7 script to create the db archive. I now have a new strategy that involves only the dump-database-d6.sh script.

In sql-dump, I will add a new option --dbtng-export that will cause sql-dump to use the aforementioned script rather than mysqldump / pgdump / etc.; it will check for dbtng first (drupal > 7 or module_exists), and I'll probably copy the script into drush rather than call it from d7; this will allow it to work from d6 to d6.

This change will make sql-sync also use this same technique to dump the database if --dbtng-export is specified to that command. sql-sync will then also need to be modified to detect --dbtng-export at sql-import time, and if it is set, then the dump file will be executed with drush php-script instead of importing with the sql-connect string.

sql-sync could also do with some minor enhancements, such as naming the dump file '.php' instead of '.sql' if appropriate, and automatically detecting the situation where the destination schema is different than the source schema, in which case --dbtng-export will be forced.

That should be pretty easy; once I have that much working, I'll go on to the archive command. The original discussion for that is here.

I'm very hesitant to complicate sql-dump and sql-sync with such an edge case as DB platform migration using drush. I recommend writing dedicated commands for this, and hosting them in drush_extras. The goal is noble, but has limited audience. It is OK to keep this issue in drush project for now, so it gets more eyeballs.

I guess it depends on how reliable (and quick) the script is. What if you had to work on a remote Drupal site hosted on Windows with SQLServer? With --dbtng-export, you could have your dev copy in mysql and run on ubuntu, and sql-sync would fix it up for you. The reverse is also true, if you want to develop on Windows but your hosting provider is running on Linux? I guess then you use mysql.

Okay, maybe this is kind of fringe. I'll do it as you say, but somehow it seems nicer to support it in sql-sync.

If I'm only supporting migrations, then I'll probably use Josh's code.

I don't know if we can really say that this is an edge case with D7 being so young still.

It was also my instinct to not put this in sql-dump, but make it its own command. That makes me think however, that maybe this suggests that our db-dumping/importing should be pluggable like drush_pm_version_control so a command like this could be implemented on its own, but still not have to re-implement the options that sql-dump already has or make our existing code more complex.

If it is going to be its own command, I'd prefer to see it as a project on its own so its more visible. Don't know how many people know about drush_extras.

I've written DBTNG Migrator: http://drupal.org/sandbox/fiasco/1113886 which I'll likely apply to have as an offical project. It replicates data from one database default connection to another.

Awesome work Josh. Interested parties should follow Josh's sandbox.

This code can now be found here: http://drupal.org/project/dbtng_migrator