Using SchemaSpy to visualize a SQLite database (windows)

Question

I am trying to make a sqlite dump with SchemaSpy. I got SchemaSpy up and running and was able to properly dump a MySQL database.

Now I have an .sqlite file (from an iOS application) and a sqlite driver from Christian Werner (http://www.ch-werner.de/javasqlite/overview-summary.html). An example over here explains how to work with that but to be honest - I don't understand what to do.

Is there someone who knows how I can use SchemaSpy on Windows? It is not necessary for me to use the GUI tool, though.

Answer 1

It's usually best to grab the latest beta version of SchemaSpy. The invocation should be similar to running against MySQL except you specify your database type with -t sqlite and point the "database" to your .sqlite file.

The SQLite drivers use JNI for their implementation, so you'll need to make sure sqlite_jni.dll is in your PATH. To temporarily add it to your PATH in a Windows command prompt:

set PATH=%PATH%;directoryContainingTheJniDll

Answer 2

I do wonder why you need to get the sqlite driver. Just tell SchemaSpy your DB type is sqlite and you shall be okay. Something like

java -jar schemaSpy.jar -t sqlite -db <your file> -o <output>

Answer 3

I also ran into this problem but finally made it work. You need the DLL for the SQLite library.

You can download it here, and then you can put it on system32 folder of your windows installation (C:\Windows\System32 for example)

Lastly, don't forget to specify the exact JDBC driver path:

java -jar… -dp "D:\SchemaSpy\driver\j2sdk1.4.2_03\jre\lib\ext\sqlite.jar" …

Answer 4

I realize this is an old question, but for the record it's possible to make SchemaSpy use the Xerial SQLite JDBC driver, without a huge amount of difficulty, by rolling a new .properties file for it, following the instructions on http://schemaspy.sourceforge.net/dbtypes.html

All you really need to do is copy the existing sqlite.properties, renaming it to something like sqlite-xerial.properties and change the relevant lines; for example:

driver=org.sqlite.JDBC

description=SQLite-Xerial

driverPath=sqlite-jdbc-3.7.2.jar

The Xerial driver doesn't need the JNI DLL file and also has the benefit of picking up any configured FK relationships correctly - at least with the SQLite 3 database I've tested it against.

It does throw up some warnings when referencing table or column names that are also keywords, but that may be SchemaSpy's fault for not wrapping them in []. Or schema designers' faults for using keywords as table and column names :)

Answer 5

I'm the author of one of the articles you mentioned.

You need to compile the schemaspy JDBC driver. It was written for UNIX but someone sent the author notes on building it in a Windows environment. But there is a set of files for windows prebuilt with the sqlite.jar and .dll you need. Once you've got that on the classpath its straightforward.

If this is all too much for you, you can always fire up the sqlite3 CLI and use the .schema command to dump the tables, and set them up in a database you do know how to connect to. Or set up and Ubuntu VM ;)