Graphical representation schema

Once in a while we get into the situation where we are given the task to work on a new database. Without knowing the design of the database it is hard to write and explain queries and gather the desired results. There is one opensource tool that can come in very handy before you dive into understanding the database design.

So I figured I will write something on Schemaspy which works very well on a majority of databases. But we will only talk about postgresql for now. Configuration and installation is pretty much the same for all other database types.

Ok so lets get started with the install process. This install is on Ubuntu 12.04 but will be pretty much the same for any other linux base OS.

Install the following dependencies:

1. vik@debian$ sudo apt-get install graphviz
2. vik@debian$ wget http://jdbc.postgresql.org/download/postgresql-9.1-902.jdbc4.jar (place the jar where you can access it with schemaspy)
3. vik@debian$ sudo apt-get install openjdk-7-jdk openjdk-7-jre

Get Schemaspy:

vik@debian$ wget http://sourceforge.net/projects/schemaspy/files/schemaspy/SchemaSpy%205.0.0/schemaSpy_5.0.0.jar/download -O schemaSpy_5.0.0.jar

Lets fire it up:

vik@debian$ java -jar schemaSpy_5.0.0.jar -t pgsql -host localhost:2525 -db test -s testschema -u postgres -p password -dp /home/vik/plugins/postgres/postgresql-9.1-902.jdbc4.jar -o /home/vik/dbgui

What does the above mean?
-t pgsql (selects pgsql as dbtype to connect)
-host (obviously where your postgresql database is use :port#)
-db (database to connect to)
-s (schema name)
-dp (this is the location where the jdbc plugin is)
-o (this is the location where schemaspy will put all the gui representation files in)

If you want your gui representation to work on your browser from any box. Get apache2 and create a symlink like so:

vik@debian$ sudo apt-get install apache2
vik@debian$ cd /var/www
vik@debian$ sudo ln -s /home/vik/dbgui test (test is the dbname you can have your own there)

Thats it access schemaspy:

http://localhost/test

Here is a schema sample output from the schemspy website:

http://schemaspy.sourceforge.net/sample/

Advertisements

,

  1. #1 by Shawn Taaj on February 12, 2013 - 5:26 am

    This is really a neat tool, I never knew it was that easy.. You have the wheels in my head turning 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: