views:

182

answers:

4

Is there an incantation of mysqldump or a similar tool that will produce a piece of SQL2003 code to create and fill the same databases in an arbitrary SQL2003 compliant RDBMS?

(The one I'm trying right now is MonetDB)

A: 

From SQL Server 2000 or 2005 you can have it generate scripts for your objects, but I am not sure how well they will transfer to other RDBMS.

Mitchel Sellers
A: 

The generate script option is probably the easiest way to go. You'll undoubtedly have to do some search/replace on a few data types though.

CodeRot
+1  A: 

This one is kind of tough. Unless you've got a very simple DB structure with vanilla types (varchar, integer, etc), you're probably going to get the best results writing a migration tool. In a language like Perl (via the DBI), this is pretty straight-forward. The program is basically an echo loop that reads from one database and inserts into the other. There are examples of this sort of code that Google knows about.

Aside from the obvious problem of moving the data is the more subtle problem of how some datatypes are represented. For instance, MS SQL's datetime field is not in the same format as MySQL's. Other datatypes like BLOBs may have a different capacity in one RDBMs than in another. You should make sure that you understand the datatype definitions of the target DB system very well before porting.

The last problem, of course, is getting application-level SQL statements to work against the new system. In my work, that's by far the hardest part. Date math seems especially DB-specific, while annoying things like quoting rules are a constant source of irritation.

Good luck with your project.

jjohn
+3  A: 

DDL statements are inherently database-vendor specific. Although they have the same basic structure, each vendor has their own take on how to define types, indexes, constraints, etc.

DML statements on the other hand are fairly portable. Therefore I suggest:

  • Dump the database without any data (mysqldump --no-data) to get the schema
  • Make necessary changes to get the schema loaded on the other DB - these need to be done by hand (but some search/replace may be possible)
  • Dump the data with extended inserts off and no create table (--extended-insert=0 --no-create-info)
  • Run the resulting script against the other DB.

This should do what you want.

However, when porting an application to a different database vendor, many other things will be required; moving the schema and data is the easy bit. Checking for bugs introduced, different behaviour and performance testing is the hard bit.

At the very least test every single query in your application for validity on the new database. Ideally do a lot more.

MarkR