visualize sqlite database schemaspy

Question

I am tring to visualize sqlite database. There are several pages discussing this but none of those solution worked for me Good tool to visualize database schema or easy visualisation using sqlfairy or SchemaDisplay. Seems that the problem is with SQLite.JDBCDriver. Here is discussion about java and sqlite but i cannot figure it out. What I have done is renaming sqlite-jdbc-3.7.2.jar to sqlite.jar hoping that helped but as you can see bad luck.

[pista@HP-PC schemaspy]$ ls -l
total 7084
drwxrwxr-x  3 pista pista    4096 Dec 12 22:34 javasqlite
drwxrwxr-x  2 pista pista    4096 Dec 12 22:05 lib
drwxrwxr-x 14 pista pista    4096 Dec 12 22:15 mendeley
drwxrwxr-x  2 pista pista    4096 Dec 12 22:13 out
drwxrwxr-x  2 pista pista    4096 Dec 12 22:05 properties
-rw-rw-r--  1 pista pista    2655 Feb 22  2009 README.TXT
-rw-rw-r--  1 pista pista  248945 Aug 16  2010 schemaSpy_5.0.0.jar
-rw-rw-r--  1 pista pista  161441 Dec 12 22:04 schemaSpyGUI20090302.zip
-rw-rw-r--  1 pista pista      26 Nov  4  2007 schemaSpyGUI.bat
-rw-rw-r--  1 pista pista  102299 Mar  2  2009 schemaSpyGUI.jar
-rw-rw-r--  1 pista pista      86 Nov 18  2007 schemaSpyGUI.sh
-rw-rw-r--  1 pista pista  284954 Dec 12 22:25 sqlite-dll-win32-x86-3070701.zip
-rw-rw-r--  1 pista pista 3201128 Dec 12 22:28 sqlite.jar
-rw-rw-r--  1 pista pista 3201128 Aug 27  2010 sqlite-jdbc-3.7.2.jar
drwxrwxr-x  3 pista pista    4096 Dec 12 22:05 src
drwxrwxr-x  2 pista pista    4096 Dec 12 22:31 xerial
drwxrwxr-x  7 pista pista    4096 Dec 12 22:31 xerial-sqlite-jdbc-e0fd4e05a97e
[pista@HP-PC schemaspy]$ java -jar schemaSpy_5.0.0.jar -t sqlite -u pista -o out/ -db mendeley/[email protected]@www.mendeley.com.sqlite 
Using database properties:
  [schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/sqlite.properties
java.lang.ClassNotFoundException: SQLite.JDBCDriver

Failed to load driver 'SQLite.JDBCDriver'from: [file:/home/pista/schemaspy/sqlite.jar]

Use the -dp option to specify the location of the database
drivers for your database (usually in a .jar or .zip/.Z).

What I am doing wrong, is this good approach ?

Answer 1

I was able to get sqlite to work with the schemaspy command line tool. I used the sqlite jdbc driver from:

https://bitbucket.org/xerial/sqlite-jdbc

My sqlite.properties file looks like:

description=SQLite
connectionSpec=jdbc:sqlite:<db>
db=database name
driver=org.sqlite.JDBC
#you may need to put the full path to the driver depending on your setup
driverPath=sqlite-jdbc-3.7.15-M1.jar
selectTablesSql=.tables

Answer 2

The JDBC driver class is org.sqlite.JDBC, not SQLite.JDBCDriver.

I would suggest that you try SchemaCrawler for SQLite, which is a free and open-source database documentation tool. The SchemaCrawler for SQLite download comes bundled with a later version of the SQLite JDBC driver, 3.7.8, and no additional renaming of jars is needed.

Sualeh Fatehi, SchemaCrawler