How The Integration Of Legacy Visual Basic App With Rails Looks Like!

What is the best way to import data from legacy external sources into our Rails app and trigger certain tasks automatically in our rails app ? A very difficult question to answer and it varies in every case.

Well, here was our challenge – The earlier process was that the new entries were being dumped into a table using VBA code and then from our Rails admin portal, we had to click a button to process these entries and load them into a new table. A very painful and manual process. To eliminate the manual process, the idea now was to trigger the migration process straight from the VBA code ie. once new entries are loaded into the from_table, our rails system should be able to kickstart the migration job and transfer them to to_table.

The first solution that came to our mind was to write database level triggers to move the data from the from_table to to_table but there were ActiveRecord callbacks in our application which affected three other tables. So rewriting all that code at the database level did not seem right.

The second solution that came to our mind was to move the code from VBA to ruby, so that we could automate the whole process. But then there was some processing going on in the VBA code before creating the row and due to business constraints, we had to work with VBA code (let sleeping lions lie). Point taken.

The next solution to have a cron job running every 30 mins was rejected by the client. He did not want to wait for that long to see the data migrated. We could have written a cron job running every 1 min, but that would have created many processes. And since the migration sometimes took more than 1 minute (depending on the data), it would have been a dangerous possibility to have 2 processes parallely doing the same thing.

Now, the big question was how to pass a message from VBA script directly to our rails app?

With a little help from the database, we designed an unorthodox solution which was easy to develop and got the job done. There might be a better and cleaner solution and we are all ears, but I am proud of what we have cooked up. So here is the recipe.

So lets divide the task into 2 main parts

1. Write a rake task to watch a table named “triggers” and trigger whenever new entry is created in this table
2. Make sure that this rake task is monitored by monit so that we don’t need to start/stop/monitor this task manually

Step 1

insert into triggers set start_time = current_timestamp

desc "Migrate new entries"
task :monitor_entries => :environment do
`echo "#{Process.pid}" > #{Rails.root}/log/monitor_entries.pid`
`echo "#{Process.ppid}" > #{Rails.root}/log/monitor_entries.ppid`
while(1)
trigger_entries = Trigger.where(end_time: nil)
if trigger_entries.length > 0
results = FromTable.process
trigger_entries.each {|trigger| trigger.update_attribute(:end_time, DateTime.now.utc) }
else
sleep 30
end
end
end

Step 2

check process job_trigger
  with pidfile /path/to/log/monitor_entries.pid
    start program = "/bin/bash -c 'export rvm_path=/path/to/.rvm; . $rvm_path/bin/rvm; cd /path/to/rails/app; bundle exec rake monitor_entries'"
    stop program = "/bin/bash -c 'kill $(cat /path/to/log/monitor_entries.pid)'"

queue "sudo monit restart job_trigger"