The main goal of this work is to implement a blob store for MBTiles version 1.1. A secondary goal will be to implement all the commune machinery needed to support SQLitle based blob stores.

#MBTiles

MBTiles is a specification that describe how to store tiles in an SQLitle database.

The MBTiles specification as some limitations that we should be aware of:

• only EPSG:3857 projection is allowed
• only png and jpg formats are allowed
• a tile doesn't have an associated create date

We can rely on GeoTools gt-mbtiles module for reading and writing MBTiles files.

#SQLitle

SQLitle database files cannot be managed as simple files. When connections to an SQLite database are open we should not delete, move or switch the associated file. Databases files can be filled with "empty space" after deleting an huge amount of data or can become fragmented after frequent inserts, updates or delete operations.

SQLite documentation warns us against putting databases files on a shared file systems (NFS) if multiple process need access to it (which is our case). Using SQLitle based blob stores with a shared storage can lead to unexpected problems.

#VACUUM and DiskQuota

To remove the fragmented space (or the empty space), the VACUUM command needs to be executed. Although, performing a VACUUM command as a few drawbacks:

- During a VACUUM twice the size of the original database file is required in disk.
- During the VACUUM operation no access to the database is allowed.
- The VACUUM operation copies the whole database which can take minutes.

For these reasons the VACUUM command cannot be performed after each operation. When possible we will avoid creating fragmented space. For example, during a truncate operation we may prefer remove a whole SQLilte file instead of deleting part of is content. Another consequence of the fragmented space is that DiskQuota will not be compatible with this blob store.

#MBTiles Granularity

Reading and writing tiles on an SQLite database will be slower than writing on a file system but will allow us to avoid file system headaches. In order to limit the amount of contention on each single MBTiles file we will allow users to decide the granularity of the files so that instead of having a single file for each single layer we will allow users to have more granularity.

MBTiles force us to have at least a file per layer and format. If we want to support more CRSs we will also need a file for each CRSs. By configuration it will be possible to configure the granularity of the database files. By default we will have a granularity per layer, crs, format and zoom level. As an instance something like this could be offered:

<blobstore>
   <file>/path/to/{grid}/{dim}/{tileset}/{z}/{x}-{y}.sqlite</file>
   <xcount>1000</xcount>
   <ycount>1000</ycount>
</blobstore>

In this case we should include the {x}, {y} and {z} replacements in the template determining the file to use. In the previous example, tile (z,x,y)=(15,3024,1534) would be stored in a file named /path/to/g/mytileset/15/3000-1000.sqlite3 and tile (5,2,8) would be stored in a file named /path/to/g/mytileset/5/0-0.sqlite3.

With more databases files we have more performance but we will have also more files to manage on the file system. In addition we can couple this with the in-memory cache in order to improve tile serving performance.

#Connection Pooling and Performance

SQLite allow multiple readers but only allow one writer at the time which will block the entire database. At most only one connection should be open to each SQLite database, the total number of open connections is limited by the number of open files allowed by the OS (in linux this is controlled by the ulimit). A connection pool that will control the number of open connections and that will be responsible to manage the connections will be implemented.

#Replace Operation

As said before, if the cache is running we cannot simply switch SQLite files, we need to make sure that all connections are closed. A replace operation will be created for this propose. The replace operation will first copy the new file side by side the old one, then block the requests to the old file, tear down the store, delete the old one, rename the new file to current one, reopen the new db file and start serving requests again. Should be almost instant. A REST entry point for this operation will be created, it will be possible to submit a zip file or a single file along with the request. We can also ask the replace operation to use an already present file or directory.

#Limitations

This blob store will have two major limitations:

- No proper integration with disk quota.
- A MBTiles store cannot be shared among several GWC instances.

As stayed before, the MBTiles specification don't give us a way to know when a tile was created. To allow expire rules we use an auxiliary table were we will store that information. In the presence of an MBTiles file generated by a third party tool we will assume that the create time of a tile was the first time it was accessed. This feature can be activated or deactivated by configuration. Note that this will not break the MBTiles specification compliance.

#Implementation

The implementation of this blob store will be self contained no changes to other GWC modules will be required. The only exception is the spring context configuration file which needs to be added to the WEB module (the same work flow as the others GWC modules).

As stayed before, the main goal of this work is to add support for MBTiles, but were possible we will abstract concepts that can be used to implement other SQLite based blob stores.

The extra dependencies added by this module will be:

• common-2.6.0.jar
• commons-jxpath-1.3.jar
• ecore-2.6.1.jar
• gt-data-16-SNAPSHOT.jar
• gt-epsg-hsql-16-SNAPSHOT.jar
• gt-graph-16-SNAPSHOT.jar
• gt-jdbc-16-SNAPSHOT.jar
• gt-mbtiles-16-SNAPSHOT.jar
• gt-xsd-core-16-SNAPSHOT.jar
• gt-xsd-fes-16-SNAPSHOT.jar
• gt-xsd-filter-16-SNAPSHOT.jar
• gt-xsd-gml2-16-SNAPSHOT.jar
• gt-xsd-gml3-16-SNAPSHOT.jar
• gt-xsd-ows-16-SNAPSHOT.jar
• hsqldb-2.3.0.jar
• net.opengis.fes-16-SNAPSHOT.jar
• net.opengis.ows-16-SNAPSHOT.jar
• org.w3.xlink-16-SNAPSHOT.jar
• picocontainer-1.2.jar
• sqlite-jdbc-3.8.6.jar
• Xsd-2.6.0.jar