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