Updating Rails / MySQL database datetime fields


We have a demo site that we show to prospective clients, and we reset it occasionally using a ‘seed database’ that we have created to give realistic reporting data.

The problem is that the timestamps on the seed database get old, which means that when you’re demo-ing, some of the reports and charts look bad.

So .. here is a little Ruby on Rails utility script that can get you an up to date looking database. It is VERY hacky so do be careful. In particular, any datetime fields you’ve created that might represent a future date will need to be excluded (we have one called ‘deadline’, you may have more.

First – back up your live demo database. It is quite easy to have run database migrations since you created your seed database.

Now, load your seed database onto your dev environment and run

rake db:migrate

And then back this up. Next, run

rails c

and paste the following into the rails command line


def update_database_times
Rails.application.eager_load!
puts "SELECT @most_recent_event := FROM_UNIXTIME(0);"
ActiveRecord::Base.descendants.each do |d|
next unless d.table_exists?
d.columns_hash.each do |k,v|
next if k == 'deadline'
puts "SELECT @most_recent_event := GREATEST(IFNULL(MAX(#{k}),@most_recent_event),@most_recent_event) FROM #{d.table_name};" if v.type == :datetime
end
end

puts "SELECT @yesterday := SUBDATE(NOW(),1);"
puts "SELECT @days_to_add := GREATEST(0,DATEDIFF(@yesterday,@most_recent_event));"
puts "SELECT @days_to_add := IF(caption='demo',@days_to_add,0) FROM clients;"

ActiveRecord::Base.descendants.each do |d|
next unless d.table_exists?
d.columns_hash.each do |k,v|
puts "UPDATE #{d.table_name} SET #{k} = DATE_ADD(#{k}, INTERVAL @days_to_add DAY);" if v.type == :datetime
end
end

nil
end

Fire up the rails console in the project you are working with. Then paste in the code above, and run it using

update_database_times

This will generate output that will look something like this:


SELECT @most_recent_event := FROM_UNIXTIME(0);
SELECT @most_recent_event := GREATEST(IFNULL(MAX(created_at),@most_recent_event),@most_recent_event) FROM users;
SELECT @most_recent_event := GREATEST(IFNULL(MAX(updated_at),@most_recent_event),@most_recent_event) FROM users;
SELECT @most_recent_event := GREATEST(IFNULL(MAX(created_at),@most_recent_event),@most_recent_event) FROM tasks;
SELECT @most_recent_event := GREATEST(IFNULL(MAX(updated_at),@most_recent_event),@most_recent_event) FROM tasks;
# more lines like these..
SELECT @yesterday := SUBDATE(NOW(),1);
SELECT @days_to_add := GREATEST(0,DATEDIFF(@yesterday,@most_recent_event));
SELECT @days_to_add := IF(caption='demo',@days_to_add,0) FROM clients;
UPDATE users SET created_at = DATE_ADD(created_at, INTERVAL @days_to_add DAY);
UPDATE users SET updated_at = DATE_ADD(updated_at, INTERVAL @days_to_add DAY);
UPDATE tasks SET created_at = DATE_ADD(created_at, INTERVAL @days_to_add DAY);
UPDATE tasks SET updated_at = DATE_ADD(updated_at, INTERVAL @days_to_add DAY);
# more lines like these..

We have put a protection line in where if the caption field in the clients database is not ‘demo’, then don’t change the datetime.

Now – try firing up your dev environment to check the database works. When you’re happy, copy it over to the demo environmnent. Add any new functionality and then back up that database to make it the new seed database.

Be careful; back everything up ahead of times, practice on localhost, and let me know if you’ve got any suggested improvements. Enjoy!

Advertisements

About saasmd

I am an experienced software-as-a-service entrepreneur, based in London, UK. I love building interesting software businesses. My current venture is StorIQ, a platform to help bricks-and-mortar retailers manage their operations more effectively. This blog is a space to share low-level techie stuff that I think other people will find useful.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s