A quick and dirty example of how to import mongo documents into a postgres hybrid document store

example_migration.rb

require 'pg'
require 'mongo'
require 'nokogiri'

mongo_conn = Mongo::Connection.new.db("qcloud_dev")
pg_conn = PG.connect(:dbname => "poc")

standards_collection = mongo_conn["standards"]
sheets_collection = mongo_conn["sheets"]

standards = standards_collection.find

def xml_for_standard(standard)
  standard_builder = Nokogiri::XML::Builder.new do |xml|
    xml.standard {
      xml.check_groups {
        standard.fetch("check_groups", []).each do |check_group|
          xml.check_group(:name => check_group["name"]) {
            check_group.fetch("checks", []).each do |check|
              xml.check(:label => check["label"], :type => check["field_type"], :id => check["_id"])
            end
          }
        end
      }
    }
  end
  standard_builder.to_xml
end

def xml_for_sheet(sheet)
  checks = []
  sheet.fetch("check_groups", []).each do |check_group|
    checks.concat(check_group.fetch("checks", []))
  end

  sheet_builder = Nokogiri::XML::Builder.new do |xml|
    xml.sheet {
      xml.checks {
        checks.each do |check|
          xml.check(:id => check["standard_check_id"]) {
            xml.text(check["value"])
          }
        end
      }
    }
  end

  sheet_builder.to_xml
end


standards.each do |standard|

  xml = pg_conn.escape_string(xml_for_standard(standard))
  query = <<-SQL
    INSERT INTO standards(name, state, created_at, updated_at, document, old_mongo_standard_id) VALUES ($1, $2, $3, $4, $5, $6)
SQL
  pg_conn.exec(query, [
    standard["standard_name"],
    standard["state"],
    standard["created_at"].to_s,
    standard["updated_at"].to_s,
    xml,
    standard["_id"]
  ])

end


sheets = sheets_collection.find


puts "Starting Sheet import"
sheets.each_with_index do |sheet, idx|

  if idx % 100 == 0
    puts "Processing sheet #{idx}..."
  end

  xml = xml_for_sheet(sheet)

  query = <<-STUFF
    INSERT INTO sheets(created_at, updated_at, standard_id, old_mongo_sheet_id, document, old_mongo_standard_id) VALUES ($1, $2,
        (SELECT id FROM standards WHERE old_mongo_standard_id = $3 LIMIT 1),
        $4, $5, $6
      )
STUFF

  pg_conn.exec(query, [
    sheet["created_at"].to_s,
    sheet["updated_at"].to_s,
    sheet["standard_id"],
    sheet["_id"],
    xml,
    sheet["standard_id"]
  ])

end