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
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:
Here is a schema sample output from the schemspy website: