DatabaseEmptier - generate DELETE statements for a MySQL database with FKs using Sequel and RGL

database_emptier.rb

require 'rubygems'
require 'sequel'
require 'rgl/adjacency'
require 'rgl/topsort'
require 'rgl/transitiv_closure'
require 'rgl/connected_components'

# Usage example below

class DatabaseEmptier
  def initialize(information_schema_db, options = {})
    @information_schema_db = information_schema_db
    @tables_to_preserve = (options[:preserve] || [ ]).map { |table_name| table_name.to_sym }
  end

  def to_ruby
    statements { |table_name|
      "Sequel::Model.db[#{table_name.inspect}].delete"
    }
  end

  def to_sql
    statements { |table_name|
      "DELETE FROM #{table_name};"
    }
  end

  private

  def statements(&block)
    tables_ordered_for_deletion.map(&block).join("\n")
  end

  def tables_to_empty
    db_tables - @tables_to_preserve
  end

  def db_tables
    @information_schema_db[:TABLES].
      select(:TABLE_NAME).
      filter(:TABLE_SCHEMA => TARGET_DATABASE).
      map(:TABLE_NAME).
      map { |table_name| table_name.to_sym }
  end

  def constraint_graph
    constraint_graph = RGL::DirectedAdjacencyGraph.new
    db_constraints.each do |constraint|
      from = constraint[:TABLE_NAME].to_sym
      to = constraint[:REFERENCED_TABLE_NAME].to_sym
      # RGL won't generate a complete topological sort if the graph contains cycles.
      # The condition here eliminates cycles from self-referential tables
      constraint_graph.add_edge(from, to) unless from == to
    end
    
    puts "Cycles detected in graph - output statements will be incomplete" unless constraint_graph.acyclic?
    
    constraint_graph
  end

  def db_constraints
    @information_schema_db[:REFERENTIAL_CONSTRAINTS].
      select(:TABLE_NAME, :REFERENCED_TABLE_NAME).
      filter(:CONSTRAINT_SCHEMA => TARGET_DATABASE)
  end

  def isolated_tables
    tables_to_empty - constraint_graph.vertices
  end

  def tables_ordered_for_deletion
    constraint_graph.topsort_iterator.to_a + isolated_tables
  end  
end

TARGET_DATABASE = 'db_to_empty'
USERNAME        = 'username'
PASSWORD        = 'password'

db = Sequel.connect("mysql://localhost/information_schema?user=#{USERNAME}&password=#{PASSWORD}")
db_emptier = DatabaseEmptier.new(db, :preserve => ["schema_info"])
puts db_emptier.to_ruby
puts db_emptier.to_sql

example_output.rb

Sequel::Model.db[:users_follows].delete
Sequel::Model.db[:ratings].delete
Sequel::Model.db[:feeds_posts].delete
Sequel::Model.db[:feeds].delete
Sequel::Model.db[:users].delete

example_output.sql

DELETE FROM users_follows;
DELETE FROM ratings;
DELETE FROM feeds_posts;
DELETE FROM feeds;
DELETE FROM users;