Module: SQLDependencyGrapher

Defined in:
lib/sql_dep_grapher.rb

Overview

SQLDependencyGrapher allows you to visualize the query dependencies between your database tables to better understand how they actually get used. It generates a graph of the connections between tables based on joins found in a SQL query log.

To generate a graph, you run the sql_dep_graph command whiche creates a dot file that you can render with Graphviz or OmniGraffle.

Usage:

sql_dep_graph log/production.log > sql_deps.dot

dot -Tpng sql_deps.dot > sql_deps.png

Then open sql_deps.png in your favorite image viewer.

You can find Graphviz here:

www.graphviz.org/

And can download it for various platforms from here:

www.graphviz.org/Download.php

Constant Summary collapse

VERSION =

The Version of SQLDependencyGrapher

'1.0.0'

Class Method Summary collapse

Class Method Details

.build_graph(stream = ARGF) ⇒ Object

Builds a Graph from a SQL query log given to stream.



40
41
42
43
44
 
# File 'lib/sql_dep_grapher.rb', line 40

def self.build_graph(stream = ARGF)
  data = collect stream
  counts = count data
  return graph(data, counts)
end

.collect(stream) ⇒ Object

Returns an Array of SQL joins from stream.



51
52
53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/sql_dep_grapher.rb', line 51

def self.collect(stream)
  data = []

  stream.each_line do |line|
    line.grep(/FROM\s+(.*?)\s+WHERE/) do
      tables = $1.split(',').reject { |t| t =~ /\(/ }
      tables = tables.map { |t| t.split(' ').first }
      data << tables if tables.size > 1
    end
  end

  return data
end

.count(data) ⇒ Object

Counts the number of times a join between two tables occurs. Returns a Hash of pair => count.



69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/sql_dep_grapher.rb', line 69

def self.count(data)
  counts = Hash.new 0

  data.each do |tables|
    tables = tables.sort
    curr = tables.shift
    tables.each do |table|
      counts[[curr, table]] += 1
    end
  end

  return counts
end

.graph(data, counts) ⇒ Object

Creates a Graph of data using counts for edge weights.



86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/sql_dep_grapher.rb', line 86

def self.graph(data, counts)
  graph = Graph.new

  max_count = counts.values.max
  solid = Math.log10(max_count).floor
  dotted = solid / 3
  dashed = solid / 3 * 2

  counts.each do |(first, second), count|
    graph[first] << second
    edge = ["weight=#{count}", "label=\"#{count}\"", "dir=none"]

    case Math.log10 count
    when 0.0..dotted then
      edge << "style = dotted"
    when dotted..dashed then
      edge << "style = dashed"
    when dashed..solid then
      edge << "style = solid"
    else
      edge << "style = bold"
    end

    graph.edge[first][second].push(*edge)
  end

  return graph
end