Often we need work on existing database schema. How to quickly master the database structure can be a challenge since we might not be able to get the data model ERD diagram from project repository.
However, as long as you get access to the database, then you should be able to generate the data model using SchemaSpy. SchemaSpy is LGPL-based, developed by John Currier in 2004. It has been improved constantly since then.
SchemaSpy is written in Java and deployed as a jar file. Current version is 6.1. It’s a command line tool. The output is a folder with html and other files which outline the database tables, views and their relationships. But before you run, you need have JDBC driver and GraphViz ready. Since SchemaSpy uses JDBC driver to connect to database and use GraphViz to generate the ER diagrams.
To get the list of available database types, you may use:
java -jar schemaspy.jar -dbhelpYou may define a text file called schemaspy.properties in the same folder where schemaspy.jar resides:
# type of database. Run with -dbhelp for details
schemaspy.t=orathin
# path to the dowloaded oracle jdbc drivers, for example
schemaspy.dp=d:\schemaspy\drivers\oracle\ojdbc6.jar
# database properties: host, port number, name user, password
schemaspy.host=orcale database host
schemaspy.port=orcale database port, usualy 1521
schemaspy.db=database name or SID
schemaspy.u=username
schemaspy.p=password
# output dir to save generated files
schemaspy.o=d:\schemaspy\output
# db scheme for which generate diagrams
schemaspy.s=scheme name
# to address ERROR - Bad config: Catalog (-cat) was not provided
schemaspy.cat=SYSAfter these, you may run:
java -jar schemaspy.jarIf everything goes smoothly, you should be able to see the content under your output folder. Double-click index.html file to review the content in your browser. Relationship tab will show you the entity-relationship diagram of your database schema.
Lionsgate Software consultants have decades of experience on data architecture design, data migration and reporting. Should you have any questions, please contact us.
