In this post I will cover the various components you'll need to migrate multiple databases with Active Record without using Rails. I'll also throw some template files up on github so you can start messing around with this as well.
To minimize confusion, be aware that whenever you see
<bla> , you as the programmer need to specify this particular piece of information, based on your environment. It is also worth noting that I'm writing this code to work in a Unix-esque environment. If you want to do this in Windows, obviously things are going to be a bit different, and I leave that horrorshow to you.
Start out by specifying your folder structure for the project. I personally like to adhere to the Rails layout and will be doing so for this example.
mkdir db mkdir db/migrate mkdir db/migrate/<name of first db> mkdir db/migrate/<name of second db> mkdir logs mkdir config touch config/database.yml touch Rakefile touch Gemfile
This file enables us to dictate our dependencies for a specific project and allows for an easy installation of these dependencies.
source 'https://rubygems.org' gem 'logger' gem 'activerecord', '= 4.1.4'
Once you've populated your Gemfile, run the following to get your dependencies installed:
This is used by our Rakefile to determine how to connect to our database(s).
YAML can be a pain due to its whitespace sensitivity. If you're having issues, grab the database.yml file off of github and roll with it.
Here's some sample code for connecting to a couple of SQLite dbs:
firstdb: adapter: sqlite3 database: <path to our first database> timeout: 5000 seconddb: adapter: sqlite3 database: <path to our second database> timeout: 5000
The migration files dictate the structure for each table in a database. They allow us to specify the name of the table, the name of each column in that table, and any attributes associated with each column. It also allows us to specify indexes if we choose to do so.
Let's start by creating a migration file to represent a single table in each database:
touch db/migrate/<name of first db>/001_create_first_table.rb touch db/migrate/<name of second db>/001_create_first_table.rb
It is important to prefix each file name with the format 00x, where x is the next number in a sequence from 1 to however many tables you have.
Open each file and specify your table's structure. Here's some example code from a school project:
require 'active_record' # Migration to create the MapObjects table class CreateMapobjects < ActiveRecord::Migration def change create_table :mapobjects do |t| t.column :object_name, :string, null: false t.column :location, :string, null: false t.column :ground_texture, :string, null: false t.column :wall_texture, :string, null: false t.column :direction, :string, null: false t.column :trap_type, :string t.belongs_to :map, null: false end add_index :mapobjects, [:object_name], :name => "index_mapobjects_on_object_name" add_index :mapobjects, [:location], :name => "index_mapobjects_on_location" add_index :mapobjects, [:ground_texture], :name => "index_mapobjects_on_ground_texture" add_index :mapobjects, [:wall_texture], :name => "index_mapobjects_on_wall_texture" add_index :mapobjects, [:direction], :name => "index_mapobjects_on_direction" add_index :mapobjects, [:trap_type], :name => "index_mapobjects_on_trap_type" add_index :mapobjects, [:map_id], :name => "index_mapobjects_on_map_id" end end
I've included two seperate migration files in github to serve as examples.
This is used for generating the tables for each database, as well as clearing those tables along with any associated data, which is handy for debugging. Also, if you're writing your application in such a way that data needs to be inserted and deleted repeatedly, this will be incredibly useful for you.
Start out with your dependencies:
#!usr/bin/env ruby require 'active_record' require 'yaml' require 'logger'
This enables us to specify which database we will be interacting with:
namespace :db do task :environment do # Take in specified database as an argument DB = ENV['db'] MIGRATIONS_DIR = 'db/migrate/' + DB end
Utilize our YAML file to connect to the specified database:
task :configuration => :environment do @config = YAML.load_file('config/database.yml')[DB] end
Establish the connection to the database and log any issues we encounter:
task :configure_connection => :configuration do ActiveRecord::Base.establish_connection @config ActiveRecord::Base.logger = Logger.new(File.open('logs/db.log', 'a')) end
The last two components are conveniently commented in the Rakefile, and therefore should be fairly self-explanatory:
desc 'Migrate the database (options: VERSION=x, VERBOSE=false, db=<name of database>).' task :migrate => :configure_connection do ActiveRecord::Migration.verbose = true ActiveRecord::Migrator.migrate MIGRATIONS_DIR, ENV['VERSION'] ? ENV['VERSION'].to_i : nil end desc 'Rolls the schema back to the previous version (specify steps w/ STEP=n).' task :rollback => :configure_connection do step = ENV['STEP'] ? ENV['STEP'].to_i : 1 ActiveRecord::Migrator.rollback MIGRATIONS_DIR, step end end # End namespace :db block
It's good to keep in mind that we are only scratching the surface of what we can do in terms of Rake commands. Please check out the sites I've included below under Resources to get more information.
Running rake commands on the command line
To clear all tables and data out of both dbs:
rake db:migrate db='<db1 name>' VERSION=0 && rake db:migrate db='<db2 name>' VERSION=0
To repopulate the tables:
rake db:migrate db='<db1 name>' && rake db:migrate db='<db2 name>'
That should do it. Again, the templates for what I've discussed here are available here. Good luck!