Wineskins

A Ruby database transfer utility built on Sequel.

Sometimes your old wine needs to be poured into new skins too.

Basic usage

From the command line, the utility runs transfer instructions for a specified source and destination database. By default, it looks for these instructions in the file ./transfer.rb. (The syntax of these instructions will be described in a moment.) So the easiest way to execute the utility is:

wskins some-db://source/url some-other-db://dest/url

This will run the instructions in ./transfer.rb to transfer the schema and/or data from the specified source database (as a URL recognized by Sequel.connect), to the specified destination database.

You can specify another transfer instructions file via --config:

wskins --config path/to/transfer.rb some-db://source/url some-other-db://dest/url

If your databases can't be opened easily via a URL, instead you can manually set the constants SOURCE_DB and DEST_DB to your Sequel databases within a ruby script, and require that script from the command line like this:

wskins --require path/to/db/setup.rb

(This is necessary for instance if you are using ADO adapters.)

Run wskins --help to see complete usage options.

How to install

Get Ruby if you don't have it.

Then

gem install wineskins

This will also install the sequel gem. If Sequel needs native drivers for your database(s), install them separately. Consult the Sequel docs for more info.

Transfer instructions syntax

A very simple case:

tables :students, :classes, :enrollments

This will copy the tables, indexes, and foreign key constraints, and then insert all the records, for the three listed tables.

To rename tables:

tables :students, [:classes, :courses], :enrollments

The classes table in the source will be renamed courses in the destination. All foreign keys referencing classes (in e.g. the enrollments table) will be changed accordingly.

If you are copying records, be careful in the order you list the tables -- this is the order that the records will be inserted. If you have foreign key constraints between two tables, you must list the target (primary key) table first to ensure that the keys exist before inserting the foreign key table records.

So in this example, the :students and :classes tables are transferred before the :enrollments table, which presumably has foreign keys to :students and :classes.

To rename fields:

table :classes, :rename => {:class_id => :id}

Primary keys, indexes, foreign keys will be changed accordingly in the destination database.

Want to copy the schema, but not import data yet?

tables :students, :classes, :enrollments, :schema_only => true

Have the schema in place, just need to import data?

tables :students, :classes, :enrollments, :records_only => true

You have finer-grained control as well:

table :students, :create_tables => true, 
                 :create_indexes => false,
                 :create_fk_constraints => false,
                 :insert_records => true

Adjusting column definitions

Sometimes you need to manually adjust column types or other options in the destination: for example due to different conventions between databases. You can pass through column definitions to Sequel's schema generator, and they will be used instead of the source database table:

table :classes do
  column :slots, :integer, :null => false, :default => 25
end

Note that in this example, all of the column definitions except slots will be copied from the source table, while slots will be defined as specified.

Excluding and including columns

(Note: not yet implemented.) You can also exclude specific columns entirely, or include only specified columns:

table :enrollments, :exclude => [:final_grade, :status]
table :students, :include => [:id, :name, :grad_year]

Although it's nearly as easy to do this manually in a hook (see below).

Limiting the imported data

(Note: not yet implemented.) It's also possible to specify a filter on the source records that get imported.

table :students do
  insert_records :grad_year => (2010..2012)
end

Filters can be anything that Sequel accepts as arguments to Dataset#filter.

Generating a transcript

If you just want a script for generating the schema later, and don't actually want to make database changes, do something like this:

transcript 'path/to/transfer.sql'
tables :schema_only => true

and include the --dry-run option on the command line.

Hooks for manual futzing

Wineskins executes a given transfer in four stages:

1. All the tables are created (:create_table)
2. All the indexes are created via alter_table (:create_indexes)
3. All the foreign key constraints are created via alter_table (:create_fk_constraints)
4. The records are inserted into each table from the source database (:insert_records)

Each of these stages has a before_* and after_* hook where you can stick whatever custom steps you need using Sequel's incredibly wide toolset, and there are also general before and after hooks that run before and after the entire transfer. You can define as many of these as you want at each hook. For instance (to turn off foreign key constraints before inserting records):

before_insert_records do
  dest.pragma_set 'foreign_keys', 'off'
end

Or to take the example above of excluding columns, you could do this manually in a callback like:

after_create_tables do
  dest[:enrollments].alter_table do
    drop_column :final_grade
    drop_column :status
  end
end

Within callbacks, the source database is referenced via source, the destination database via dest.

A note on the syntax

In the examples above I've used both a 'hash-options' style and a block syntax. Either can be used interchangably and even in combination if you want (although it's ugly looking). The options set in the block always override the options hash. Also, note that custom column definitions must be done within a block.

Motivations

This tool aims to simplify transferring data between databases, and is designed around the canonical case where the destination database is completely empty, and you want to set up everything the way it is in the source and then import the data. Of course, many other scenarios are possible, but the point is that the only things you should need to specify are either (1) differences from this scenario, or (2) differences between database adapters that Sequel cannot handle automatically.

Note that accordingly, if schema or records already exist in your destination, you are responsible for dealing with this in whatever way makes sense for your scenario. No tables, indexes, constraints, or records are automatically deleted in the destination.

So, you might want to wipe out and replace what exists (via drop_table, dest[:table].delete, etc. in callbacks); or you might want to keep what exists (omitting changes via :schema_only, :records_only options, etc.); or you might want to alter what exists (via custom alter_table, dest[:table].filter(some_filter).delete, etc. in callbacks).

The principle is that as much as possible, the source database should determine the schema of the destination database, thus minimizing manually-entered (and possibly incorrect) schema definition code. Also it helps avoid, for simple but typical cases, the great pain and knashing of the teeth involved in massaging the source data into the right format for for importing.

Alternatives / Similar projects

• Sequel's schema dumper extension lets you dump and load schema using Sequel's migration DSL.
• DbCopier, apparently unmaintained?
• Linkage mimics joins between tables in different databases.

Please help

This is a young young project, don't expect it will work out of the box without some futzing. It's only been formally tested on Sqlite to Sqlite transfers, and ad-hoc tested on a 'real' MS Access to Sqlite transfer.

If you start using it and run into weird shit, at the very least let me know about it. Better still if you send some informed guesses as to what's going on. Pull requests are awesome and going the extra mile and all that... but before you go to the trouble, unless it's a really minor fix, let me know about the issue, I might be able to save you some time and we can have a conversation about it you know?

There's a TODO list in the project root if you want to see where I'm thinking of heading, comments welcome.

Requirements

• ruby >= 1.8.7
• sequel ~> 3.0 (note >= 3.39 needed for MS Access source databases)
• progressbar (optional)