sql2dot - converts sqldumps to graphiz, in order to render an image showing sql-table & relations

sql2dot

#!/bin/bash
# Copyright 2012, Leon van Kammen (Coder of Salvation), All rights reserved.
# 
# this utility can generate database table images from sql-files.
# It converts sqldump into graphviz layout (dot) format.
# Once having a graphviz file, you can convert it to many image formats (svg/png) or view it
# interactively using canviz.
#
# Redistribution and use in source and binary forms, with or without modification, are
# permitted provided that the following conditions are met:
# 
#    1. Redistributions of source code must retain the above copyright notice, this list of
#       conditions and the following disclaimer.
# 
#    2. Redistributions in binary form must reproduce the above copyright notice, this list
#       of conditions and the following disclaimer in the documentation and/or other materials
#       provided with the distribution.
# 
# THIS SOFTWARE IS PROVIDED BY Coder of Salvation ``AS IS'' AND ANY EXPRESS OR IMPLIED
# WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
# FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> OR
# CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
# CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
# SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
# ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
# NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
# ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
# 
# The views and conclusions contained in the software and documentation are those of the
# authors and should not be interpreted as representing official policies, either expressed
# or implied, of Coder of Salvation 
#

#!/bin/bash

if [ ${#1} == 0 ]; then printf "usage: $0 generate <sqlfile> [dbprefix]\n\nthis cmd produces a .dot file which lets you generate many image formats like: \n\t'dot -Txdot out.gv > out-withlayout.dot'\n\t'dot -Tpng out.gv > out.png'\n"; exit; fi

snippet_node_open=' [ style = "filled" penwidth = 1 gradientangle = 90 fillcolor = "white:yellow" fontname = "Courier New" shape = "Mrecord" label = "'
snippet_node_name='  \"%s\" %s<%s> ****** %s ******'
snippet_node_close='" ];'
snippet_node_col=' | <%s> %s'
snippet_node_relation='[ penwidth = 1 fontsize = 14 fontcolor = "grey28" label = "1:*" ]'
dot_header='#Command to get the layout: "dot -Gsize=10,15 -Tpng thisfile > thisfile.png"

digraph g {
  graph [fontsize=30 labelloc="t" label="" splines=true overlap=false rankdir = "LR"];
' 
  #node [ fillcolor="white", color="black", fontcolor="black", labelfontcolor="red", pencolor="black"];
dot_footer='
}'

generate(){
  mysqldump="$1"
  dbprefix="$2"
  outfile="out.gv"
  cur_node=""
  nodenames=""
  node_cols=""
  node_relations=""
  nodes=0
  parsing_colums=0
  : > "$0.tmp"
  printf "[x] parsing..\n"
  echo "$dot_header" > "$outfile"
  outtext=""
  text=$(cat "$mysqldump" | grep -iv "INSERT" );
  echo "$text" | while read line; do
    tput el; printf "\r[x] processing '%s'" "$line"
    if (echo "$line" | grep -i "CREATE TABLE" > /dev/null ); then
      cur_node=$(echo "$line" | grep -i "CREATE TABLE" | sed -e 's/CREATE TABLE //gI;s/`//g;s/ .*//g;' )
      [[ $nodes > 0 ]] && printf "$snippet_node_close\n" >> "$outfile"
      node_cols=""; parsing_columns=1;
      printf "$snippet_node_name" "$cur_node" "$snippet_node_open" "$cur_node" "$(echo "$cur_node" | tr '[:lower:]' '[:upper:]' | sed 's/./&   /g')" >> "$outfile"
      tput el; printf "\r[x] found '$cur_node'                                      \n"
      nodenames="$nodenames $cur_node"
      ((nodes=nodes+1))
    fi
    echo "$line" | grep -i ";"  > /dev/null && ( parsing_columns=0;)
    if [[ $parsing_columns ]]; then
      if [[ "$line" = *,* ]]; then
        printf "$snippet_node_col" $(echo "$line" | sed 's/`//g;s/ .*//g')  "$line" | sed "s/,//g;s/\"/'/g"  >> "$outfile"
      fi
      if echo "$line" | grep -i '_id`' | grep -vE "(PRIMARY|KEY)" > /dev/null; then
        target_node=$(echo "$line" | sed 's/_id//g;s/`//g;s/ .*//g')
        target_node=$( echo "$text" | grep -E "CREATE.*$dbprefix.*$target_node\`"| sed -e 's/CREATE TABLE //gI;s/`//g;s/ .*//g;' )
        if [[ "$target_node" != "$cur_node" && ${#target_node} > 0 ]]; then
          printf "  \"%s\":\"$(echo "$line"|sed 's/`//g;s/ .*//g')\" -> %s %s\n" "$cur_node" "$target_node" "$snippet_node_relation" >> "$0.tmp"
          printf "\r[x] guessed relationship  %s -> %-60s\n" "$cur_node" "$target_node"
        fi
      fi
    fi
  done
  #echo "$outtext" | sed "s/'/\"/g" >> "$outfile"
  printf "$snippet_node_close\n"  >> "$outfile"
  cat "$0.tmp" >> "$outfile"
  echo "$dot_footer" >> "$outfile"
  #generatedot | while read line; do tput el; printf "\r[x] generating png/svg image: $line"; done
  printf "\n[x] done\n"
}

"$@"