(sqlite-jdbc) Accessing a read-only database in a zip/jar file

[Hideki]

Dear Xerial developers,

Thank you for releasing a useful set of softwares. I'm especially a
fan of sqlite-jdbc.

I imagine there would be a lot of scenarios where embedded DB is
distributed with the code, packaged as a jar file. However, it doesn't
seem current sqlite-jdbc have a capability to access db in zip/jar
yet. As a minor request, it would be great if you could support JDBC
subsubprotocol that looks like following:

Connection connection = DriverManager.getConnection("jdbc:sqlite:jar:
(jarFile)database");

FYI, Apache Derby provides access to a read-only database in a zip/jar
file in a following way:

> To access a database in a zip/jar, you specify the jar in the subsubprotocol.
> jdbc:derby:jar:(pathToArchive)databasePathWithinArchive
>
> The pathToArchive is the absolute path to the archive file. The databasePathWithinArchive
> is the relative path to the database within the archive. For example:
>
> jdbc:derby:jar:(C:/dbs.jar)products/boiledfood
> jdbc:derby:jar:(C:/dbs.jar)sales

Cited from http://db.apache.org/derby/docs/10.2/devguide/cdevdeploy11201.html


Thanks and best regards,

-Hideki Shima

[Taro L. Saito]

Hi Hideki,

I like your idea to embed sqlite DB files inside the jar file and
provide the capability to access them through JDBC.

A quick solution to this problem would be as follows:

1. If the JDBC address starts with jdbc:slite:jar:(something), extract
the DB file inside the jar file to a temporary folder.
2. sqlite-jdbc will creates a connection to the DB file in the temporary folder.

This technique is also used in my sqlite-jdbc code to load a native
library inside the jar file.

Technically speaking, SQLite uses some system calls to access DB
files, so it is impossible for SQLite to directly access DB files
inside a jar file.

A major concern is the DB extraction performance; my approach may be slow
for large volumes of DB files.

Hideki, how large your DB files?
I guess DB files upto ~20MB can be extracted in a second in Java.

--
Taro L. Saito
<l...@xerial.org>
University of Tokyo
http://www.xerial.org/leo
Tel. +81-47-136-4065 (64065)

[Hideki Shima]

Taro-san,

Thanks for suggesting the solution.

> A major concern is the DB extraction performance; my approach may be slow
> for large volumes of DB files.

I've done a tiny experiment based on org.sqlite.SQLiteJDBCLoader and
it worked reasonably fast for my purpose.

It took me about 10-11 seconds (Pentinum4, 3GHz, 1GB RAM, Windows XP,
JDK6, Runtime.getRuntime().totalMemory() -
Runtime.getRuntime().freeMemory() = 500KB ) to extract the 90MB DB
file.

Below is the code I used FYI.

public class Unjar {
public static void main(String[] args) {
long t0 = System.currentTimeMillis();
String tempFolder = new
File(System.getProperty("java.io.tmpdir")).getAbsolutePath();
File extractedFile = new File(tempFolder, "sqlite.db");
try {
InputStream reader = Unjar.class.getResourceAsStream("/sqlite.db");
FileOutputStream writer = new FileOutputStream(extractedFile);
byte[] buffer = new byte[1024];
int bytesRead = 0;
while ((bytesRead = reader.read(buffer)) != -1) {
writer.write(buffer, 0, bytesRead);
}
writer.close();
reader.close();
} catch ( Exception e ) {
e.printStackTrace();
}
long t1 = System.currentTimeMillis();
System.out.println( "Extraction done in "+(double)(t1-t0)/1000D + " sec." );
}
}

Thanks,

-Hideki