Database comparison using Sinatra

Alan deLevie posted a useful bit of Sinatra code for documenting the tables in a database in Database documentation in 50 lines of Sinatra.
I built on his code, making it database-agnostic while also turning it into a tool for comparing database structures. In the process the line count nearly doubled, so now we have database documentation in (almost) 100 lines!

It has been tested against Mysql, Postgresql and Sqlite databases.
Here is the code in its entirety:

require 'sinatra'
require 'active_record'

ActiveRecord::Base.establish_connection(
  :adapter => 'mysql',
  :encoding => 'utf8',
  :database => 'db_name',
  :username => 'user_name',
  :password => 'pswd',
  :host => 'localhost'
)

class Table
  attr_accessor :name
  
  def initialize(name)
    @name = name
  end

  def columns
    ActiveRecord::Base.connection.columns(@name).map {|c| [c.name, c.sql_type, c.null ? 'NULL' : 'NOT NULL'] }
  end
end

def list_of_tables
  tables = []
  ActiveRecord::Base.connection.tables.each do |t|
    tables << Table.new(t)
  end
  tables.sort_by {|t| t.name }
end

get '/' do
  @tables = list_of_tables
  @dbname = ActiveRecord::Base.connection.instance_variable_get(:@config)[:database].split('/').last
  
  erb :index
end

get '/sorted' do
  @tables = list_of_tables
  @dbname = ActiveRecord::Base.connection.instance_variable_get(:@config)[:database].split('/').last
  
  erb :indexsorted
end

__END__

@@ index
<style type="text/css"> table {border-collapse:collapse;} td {padding: 0 12px; color: #444;} th {color: #303030; font-size:120%; background:#B0C5E0; padding: 4px 8px 8px; text-align:left; border-top: thin solid #bbb; border-left: thin solid #bbb; border-bottom: thin solid #555; border-right: thin solid #555;} tr.data:hover {background:#B0C5E0;} </style>
<a href="/sorted">Go to list with sorted fields</a>
<hr />
<h2>Tables in <em><%= @dbname %></em></h2>
<table>
<% @tables.each_slice(5) do |table| %>
<tr><td>
<%= table.map {|t| "<a href='##{t.name}'>#{t.name}</a>" }.join('</td><td>') %>
</td></tr>
<% end %>
</table>
<hr /><br />
<table>
<% @tables.each do |table| %>
<tr><th colspan="3"><a name="<%= table.name %>"><%= table.name %></a></th></tr>
<% table.columns.each do |column| %>
<tr class="data"><td><strong><%= column[0] %></strong></td><td><i><%= column[1] %></i></td><td><%= column[2] %></td></tr>
<% end %>
<tr><td colspan="3">&nbsp;</td></tr>
<% end %>
</table>


@@ indexsorted
<style type="text/css"> table {border-collapse:collapse;} td {padding: 0 12px; color: #444;} th {color: #303030; font-size:120%; background:#B0C5E0; padding: 4px 8px 8px; text-align:left; border-top: thin solid #bbb; border-left: thin solid #bbb; border-bottom: thin solid #555; border-right: thin solid #555;} tr.data:hover {background:#B0C5E0;} </style>
<a href="/">Go home</a>
<hr />
<h2>Tables in <em><%= @dbname %></em> with fields sorted alphabetically</h2>
<table>
<% @tables.each_slice(5) do |table| %>
<tr><td>
<%= table.map {|t| "<a href='##{t.name}'>#{t.name}</a>" }.join('</td><td>') %>
</td></tr>
<% end %>
</table>
<hr /><br />
<table>
<% @tables.each do |table| %>
<tr><th colspan="3"><a name="<%= table.name %>"><%= table.name %></a></th></tr>
<% table.columns.sort.each do |column| %>
<tr class="data"><td><strong><%= column[0] %></strong></td><td><i><%= column[1] %></i></td><td><%= column[2] %></td></tr>
<% end %>
<tr><td colspan="3">&nbsp;</td></tr>
<% end %>
</table>

(The code is also available from this gist at GitHub)

To compare two database structures

• Set the ActiveRecord connection parameters to whatever you require for the first database.
• Run: ruby dbstruct.rb (or whatever you’ve named the file)
• Browse to http://localhost:4567/sorted
• Copy the text to the left pane of your diff viewer (Meld or something similar).
• Stop the app, change the connection parameters for the second database.
• As before: run, refresh in the browser and copy the page to the right pane of your diff viewer.

Because the tables are in alphabetical order and the fields of each table are also listed alphabetically, the diff viewer should give a quick idea of which tables have been added/removed and which fields have been added/removed.

Advertisement