6

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.

demongolem
  • 9,474
  • 36
  • 90
  • 105
Thorben
  • 6,871
  • 2
  • 19
  • 19

5 Answers5

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" …
nsswaga
  • 31
  • 4
1

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 :)

Chris Crowther
  • 11
  • 1
1

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 ;)

jldugger
  • 2,339
  • 6
  • 22
  • 24
  • Thanks for your help. So I tried to run those prebuilt files first which failed (running win 64bit but files are 32bit) and then to compile the source with Visual Studio 2005 which failed to. 6 Errors like this one: .\SQLite\JDBC2x\JDBCPreparedStatement.java:21: SQLite.JDBC2x.JDBCPreparedStatement is not abstract and does not override abstract method setNClob(int,java.io.Re ader) in java.sql.PreparedStatement – Thorben May 13 '11 at 10:59
  • How to use your second approach with CLI? If I dump the tables first, are iOS specific relations between them kept? – Thorben May 13 '11 at 11:00
  • I'm not sure what an iOS specific relation would be. Keep in mind that sqlite databases rarely enforce any foreign key constraints, so most FK relationships out of schemaspy will be guesswork. – jldugger May 13 '11 at 13:57
0

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>

Peon the Great
  • 1,289
  • 1
  • 10
  • 17
  • 1
    Please see my comment above. It doesn't work that way. I tried displaying an sqlite file from an iOS app. Additionally, I was asked to add the option -u to specifiy a certain user. Anyone knows what this might be? – Thorben Apr 21 '11 at 14:04
0

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

johncurrier
  • 348
  • 3
  • 7
  • I did that but received a ClassNotFoundException: SQLite.JDBCDriver. Seems like he is looking for an sqlite.jar file which is obviously not there... – Thorben Apr 21 '11 at 14:01