#
# Simple Dataset importer/exporter for use while developing a site.
#
# By Dominik Grabiec, dominik@dominikgrabiec.com
# See http://www.dominikgrabiec.com for more information.
#
# Exports the data to CSV files located in the db/dataset directory.
# Can specify a specific subdirectory with the DATASET="dir" on the command line
# when running the rake task.
#
# Each table in the database is its own CSV file, this allows the migrations
# and the database to be changed and then the data reloaded with only minimal
# modifications.
#
# This code is in the public domain.
#
namespace :dataset do
  
  # Default base directory to export/import from
  DATASET_PATH = "db/dataset"
  
  # Ruby on Rails tables to ignore exporting/importing
  SKIP_TABLES = ["schema_migrations"]
  
  
  desc "Exports the database contents into CSV files."
  task :export => :environment do
    require "FasterCSV"
    require "FileUtils"
    
    path = File.join(DATASET_PATH, (ENV["DATASET"] || "."))
    
    FileUtils.mkdir_p(path)
    
    ActiveRecord::Base.establish_connection
    database = ActiveRecord::Base.connection
    
    (database.tables - SKIP_TABLES).each do |table_name|
      FasterCSV.open(File.join(path, "#{table_name}.csv"), "w") do |csv|
        # Write column names
        csv << database.columns(table_name).map(&:name)
        # Write rows
        database.select_rows("SELECT * FROM %s" % table_name).each { |row| csv << row }
      end
    end
  end
  
  
  desc "Imports data from CSV files into the database."
  task :import => :environment do
    require "FasterCSV"
    
    path = File.join(DATASET_PATH, (ENV["DATASET"] || "."))
    
    ActiveRecord::Base.establish_connection
    database = ActiveRecord::Base.connection
    tables = (database.tables - SKIP_TABLES)
    
    database.disable_referential_integrity do
      Dir.chdir(path) do
        Dir.glob("*.csv") do |file_name|
          table_name = file_name[/(.+)\.csv/, 1]
          next unless tables.include?(table_name)
          
          rows = FasterCSV.read(file_name)
          
          # Construct a list of column headings from the first row
          heading_row = rows.shift.map { |heading| database.quote_column_name(heading) }.join(",")
          
          rows.each do |row|
            # Need to escape the data correctly so that inserting is successful
            row_data = row.map { |item| database.quote(item) }.join(",")
            database.execute("INSERT INTO %s(%s) VALUES (%s);" % [table_name, heading_row, row_data])
          end
          
        end
      end
    end
    
  end
  
end