More info about database in zip files?

[Andrea Aime]

Hi,
I've noticed that the latest version of h2 supports
read only databases in zip files. Very interesting,
I have a use case for that (the GeoTools spatial
reference systems database, which is a port of
the official EPSG database from Access to Hsql
at the moment).

I'm looking for more information thought. How
does one create the zipped read only version,
just zip an existing db?
What about performance, startup, eventual index
usage? Is the db loaded into memory for operation?

Cheers
Andrea

[Thomas Mueller]

Hi,

I will add the following documentation:

Read Only Databases in Zip or Jar File

To create a read-only database in a zip, first create a regular
persistent database, and then create a backup. If you are using a
database named 'test', an easy way to do that is using the BACKUP SQL
statement:

BACKUP TO 'data.zip'

Afterwards, you can log out, and directly open the database in the zip
file using the following database URL:

jdbc:h2:zip:data.zip!/test

Databases in a zip file are read-only. The performance for some
queries will be slower than when using a regular database, because
random access in zip files is not supported (only streaming). How much
this affects the performance depends on the queries and the data. The
database is not read in memory, so large databases are supported as
well. The same indexes are used than when using a regular database.

Does this answer your questions?

Thanks,
Thomas

[Vuong]

Hi Thomas,

In case of small database, can I load the database in zip file into an
in-memory database, so it could be access faster?

Thanks

Vuong

[Thomas Mueller]

Hi,

> load the database in zip file into an
> in-memory database, so it could be access faster?

Sure. I suggest to use a compressed script:

- Create the database
- Create a compressed script using:
script to 'c:/temp/backup.zip' compression zip
- Close the database

- Open an in-memory database (jdbc:h2:mem:test)
- Load the data from script:
runscript from 'c:/temp/backup.zip' compression zip

Loading the database will be slower, but accessing it will be faster.

Thomas