pisculichi/schemacrawler-sqlite-macos-howto.md

## schemacrawler-sqlite-macos-howto.md

      
    Raw
  

              schemacrawler-sqlite-macos-howto.md
            
          
    Generating cool SQLite database diagrams with schemacrawler on Mac OS

This was tested on MacOS 10.13
schemacrawler is a free and open-source database schema discovery and comprehension tool. It can be invoked from the command-line to produce, using GraphViz, images/pdfs from a SQLite (or other database type) file.
It can be used from the command-line to generate schema diagrams like these:

To see more examples of commands and diagrams, visit scheacrawler's docs: http://www.schemacrawler.com/diagramming.html
Install graphviz dependency

For schema drawing, schemacrawler uses graphviz, which can be installed via the Homebrew package manager:
brew install graphviz
Installing schemacrawler as a command-line tool

This section gives an example of how to install schemacrawler so that you can invoke it with your shell. There isn't a Homebrew recipe, so the shell commands basically:

Download a release zip from schemacrawler/releases
Copies the relevant subdir from the release into  /usr/local/opt/schemacrawler
Creates a simple shell script that saves you from having to run schemacrawler via the java executable
symlinks this shell script into /usr/local/bin

The rest of these instructions assume you have these directories created (which you probably do if you are using Homebrew, and that you have write access to them:
/usr/local/opt
/usr/local/bin

Additionally, I assume that /usr/loca/bin is part of $PATH, which, again, it probably is if you are using Homebrew
Download and install schemacrawler

The latest releases can be found on the Github page:
https://github.com/schemacrawler/SchemaCrawler/releases/
# set up some variables to reduce redundancy

_SCH_VERSION='14.21.02'
_SCH_BNAME="schemacrawler-${_SCH_VERSION}-distribution"
_SCH_TNAME="/tmp/${SCH_BNAME}.zip"
_SCH_URL="https://github.com/schemacrawler/SchemaCrawler/releases/download/v${_SCH_VERSION}/${_SCH_BNAME}.zip"
_SCH_DIR='/usr/local/opt/schemacrawler'
_SCH_SH="${_SCH_DIR}/schemacrawler-macos-opt.sh"

# Download and unzip into /tmp
curl -Lo ${_SCH_TNAME} ${_SCH_URL}
unzip ${_SCH_TNAME}

# Move subdir from release package into /usr/local/opt
mkdir -p ${_SCH_DIR}
cp -r /tmp/${_SCH_BNAME}/_schemacrawler/ ${_SCH_DIR}

# create the shell script manually
echo '#!/usr/bin/env bash' > ${_SCH_SH}
echo "SC_DIR=${_SCH_DIR}" >> ${_SCH_SH}
echo "java -cp \$(echo \$SC_DIR/lib/*.jar | tr ' ' ':'):\$SC_DIR/config schemacrawler.Main \"\$@\"" \
  >> ${_SCH_SH}

# make it executable and symlink it

chmod +x ${_SCH_SH}
ln -sf ${_SCH_SH} /usr/local/bin/schemacrawler  
General usage

  schemacrawler -server sqlite \
    -database DBNAME.sqlite \
    -user -password \
    -infolevel standard \
    -command schema \
    -outputformat png \
    -outputfile OUTPUT_IMAGE_FILE.png
Bootload a database

Just in case you don't have a database to playround with
echo '''
DROP TABLE IF EXISTS business;
DROP TABLE IF EXISTS inspection;
DROP TABLE IF EXISTS violation;

CREATE TABLE business (
    business_id TEXT,
    name TEXT,
    address TEXT,
    city TEXT,
    postal_code TEXT,
    latitude DECIMAL,
    longitude DECIMAL,
    phone_number TEXT,
    application_date TEXT,
    owner_name TEXT
);

CREATE TABLE inspection (
    business_id TEXT,
    "Score" NUMERIC,
    date TEXT NOT NULL,
    type TEXT NOT NULL,
    FOREIGN KEY(business_id) REFERENCES business(business_id)
);

CREATE TABLE violation (
    business_id TEXT,
    date TEXT,
    "ViolationTypeID" TEXT,
    risk_category TEXT,
    description TEXT,
    FOREIGN KEY(business_id, date) REFERENCES inspection(business_id, date)
);''' \
  | sqlite3 /tmp/tmpdb.sqlite
schemacrawler -server sqlite \
  -database /tmp/tmpdb.sqlite \
  -user -password \
  -infolevel standard \
  -command schema \
  -outputformat png \
  -outputfile /tmp/mytmpdb.png

graphviz properties

You can edit config/schemacrawler.config.properties:
88667/how-to-influence-layout-of-graph-items

schemacrawler.format.no_schemacrawler_info=true
schemacrawler.format.show_database_info=true
schemacrawler.format.show_row_counts=true
schemacrawler.format.identifier_quoting_strategy=quote_if_special_characters

schemacrawler.graph.graphviz.nodes.ranksep=circo
schemacrawler.graph.graphviz.graph.layout=circo
schemacrawler.graph.graphviz.graph.splines=ortho


schemacrawler.graph.graphviz.node.shape=folder
schemacrawler.graph.graphviz.node.style=rounded,filled
schemacrawler.graph.graphviz.node.fillcolor=#fcfdfc
#schemacrawler.graph.graphviz.node.color=red

schemacrawler.graph.graphviz.graph.fontname=Helvetica Neue
schemacrawler.graph.graphviz.node.fontname=Consolas
schemacrawler.graph.graphviz.edge.fontname=Consolas
schemacrawler.graph.graphviz.edge.arrowsize=1.5